0

I have columns which are a primary key and a plain column on a table. I want to maintain integrity about two columns.

Suppose I have a user table and two datas. I want to have integrity between a id column and a 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');

The result is

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

If I update id(a primary key), It doesn't have integrity.

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

The result is

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

But I want to this on a table. Is it possible? I don't want additional update queris

| id       | create_user |
| -------- | ------------|
| master   | master      |
| user01   | master      |
bittap
  • 518
  • 1
  • 6
  • 15
  • and you dont want to write ```triggers```? – Ibrahim Hammed Apr 03 '22 at 05:47
  • @IbrahimHammed No, I only don't want that the user should remember additional task for that. – bittap Apr 03 '22 at 05:55
  • @Carmel User will use system as previously, triggers works automatically – Justinas Apr 03 '22 at 06:11
  • 1
    Why would you need to update a primary key value? That's not something you normally do... Also what is `create_user` and what's it's relation to the primary key ? – SOS Apr 03 '22 at 06:37
  • @Justinas I tried the trigger. But It doesn't work, I get a error "Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger". Because I use the trigger for same table. https://stackoverflow.com/questions/15300673/mysql-error-cant-update-table-in-stored-function-trigger-because-it-is-already – bittap Apr 03 '22 at 07:43
  • @SOS the create_user column shows who makes the data on USER table. So, I have better not think that a primary key is not changed? the foreign key has "UPDATE CASCADE". Because I think a primary key can be changed, rarely. – bittap Apr 03 '22 at 07:54
  • @SOS in my case, I want to change id of system admin from system to master. – bittap Apr 03 '22 at 08:01
  • 1
    You must provide your "integrity" definition. Create verbal definition which sets this integrity rule completely. PS. Now this can be easily solved by additional *values* table. – Akina Apr 03 '22 at 08:27
  • @SOS Thank you for your advice. "a PK (primary key) is to have a unique identifying value that doesn't change, so it shouldn't be modified", "he value user01 isn't a good choice for a PK". I will change pk from id to no! – bittap Apr 03 '22 at 08:34
  • 1
    @Carmel - Sorry I accidentally deleted my comment instead of edit. Not sure I follow what you're saying... However, if I'm understanding the table structure correctly, then `id` as the PK, should be an unique value, like an `auto_increment` column. The username i.e. "user01" should be stored in a separate column, possibly with a unique index. If `create_user` column contains a user `id` value, then it should be a nullable numeric column. For example https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1a6b0b5e4e0b7cf1274ab1bc9148eca7 – SOS Apr 03 '22 at 08:46
  • You have a logical inconsistency you could create a foreign key on create_user referencing id with on update cascade but and update will throw an error because mysql checks that system exists as id before attempting the change. And it seems myasql will ignore on update cascade if set foreign_key_checks are set to off. – P.Salmon Apr 03 '22 at 10:00
  • https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=69373faba07b7a8c0f45400d2787e630 – P.Salmon Apr 03 '22 at 10:06

1 Answers1

0

You can update as many columns as you like and use case statements to decide what to set them to

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)
P.Salmon
  • 17,104
  • 2
  • 12
  • 19