Way to maintain integrity between two columns in MySQL table

Question

I have a column on the table, which are primary key and ordinary column. I want to maintain the integrity of these two columns.

Suppose I have a user table and two data. I want to maintain integrity between the id column and the create_user

column

CREATE TABLE USER (
    id varchar(10) not null,
    create_user varchar(10) not null,
    PRIMARY KEY (id)
);

insert into USER (id,create_user) values ('system','system');
insert into USER (id,create_user) values ('user01','system');

结果如下

| id       | create_user |
| -------- | ------------|
| system   | system      |
| user01   | system      |

If I update the id (primary key), it does not have integrity.

update USER SET id='master' WHERE id='system';

结果如下

| id       | create_user |
| -------- | ------------|
| master   | system      |
| user01   | system      |

ButI want to implement this on the form. is it possible? I don't want additional update queries.

| id       | create_user |
| -------- | ------------|
| master   | master      |
| user01   | master      |

Answer 1

You can update any number of columns and use case statements to decide what you want them to be set to

如果存在表t，则删除表t;
CREATE TABLE t (
    id varchar(10) not null,
    create_user varchar(10) not null,
    PRIMARY KEY (id)
);

向t表插入数据 (id,create_user) values ('system','system');
向t表插入数据 (id,create_user) values ('user01','system');

更新t表
SET id = case when id= 'system' then 'master' else id end ,
    create_user = case when create_user = 'system' then 'master' else create_user end 
where id = 'system' or create_user = 'system';

从t表中查询数据;

+--------+-------------+
| id     | create_user |
+--------+-------------+
| master | master      |
| user01 | master      |
+--------+-------------+
共有2行数据 (0.001 秒)