如何在mysql中保持表上两列之间的完整性

Question

我在a table上有列a primary key和a plain column。我想维护integrity大约两列。

假设我有一个用户表和两个数据。我想在id列和create_user列之间有integrity

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');
The result is
| id       | create_user |
| -------- | ------------|
| system   | system      |
| user01   | system      |

如果我更新id(主键(，它就没有完整性。

update USER SET id='master' WHERE id='system';
The result is
| id       | create_user |
| -------- | ------------|
| master   | system      |
| user01   | system      |

但是我想在a table上这样。有可能吗？我不想要额外的更新queris

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

Answer 1

您可以随心所欲地更新列，并使用用例语句来决定将它们设置为的内容

drop table if exists t;
CREATE TABLE t (
id varchar(10) not null,
create_user varchar(10) not null,
PRIMARY KEY (id)
);
insert into t (id,create_user) values ('system','system');
insert into t (id,create_user) values ('user01','system');
update 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';
select * from t;
+--------+-------------+
| id     | create_user |
+--------+-------------+
| master | master      |
| user01 | master      |
+--------+-------------+
2 rows in set (0.001 sec)