56,677
社区成员
发帖
与我相关
我的任务
分享
mysql> create table c (
-> u_id varchar(10) ,
-> u_name varchar(20) ,
-> FOREIGN KEY (u_id) references a(u_id) on update cascade,
-> FOREIGN KEY (u_name) references a(u_name) on update cascade
-> );
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> create table a (
-> u_id varchar(10) primary key,
-> u_name varchar(20) unique key
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into a values ('zhk','zhp_king1');
Query OK, 1 row affected (0.01 sec)
mysql> insert into a values ('king','zhp_king2');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> create table b (
-> u_id varchar(10) ,
-> u_name varchar(20) ,
-> FOREIGN KEY (u_id) references a(u_id) on update cascade,
-> FOREIGN KEY (u_name) references a(u_name) on update cascade
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into b values ('zhk','zhp_king1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into b values ('king','zhp_king2');
Query OK, 1 row affected (0.01 sec)
mysql> select * from a;
+------+-----------+
| u_id | u_name |
+------+-----------+
| zhk | zhp_king1 |
| king | zhp_king2 |
+------+-----------+
2 rows in set (0.00 sec)
mysql> select * from b;
+------+-----------+
| u_id | u_name |
+------+-----------+
| zhk | zhp_king1 |
| king | zhp_king2 |
+------+-----------+
2 rows in set (0.00 sec)
mysql>
mysql> update a set u_id='zhk_1' where u_id='zhk';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from b;
+-------+-----------+
| u_id | u_name |
+-------+-----------+
| zhk_1 | zhp_king1 |
| king | zhp_king2 |
+-------+-----------+
2 rows in set (0.00 sec)
mysql> update a set u_name='zhp_king22' where u_id='king';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from b;
+-------+------------+
| u_id | u_name |
+-------+------------+
| zhk_1 | zhp_king1 |
| king | zhp_king22 |
+-------+------------+
2 rows in set (0.00 sec)
mysql> update a set u_id='king222', u_name='zhp_king222' where u_id='king';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from b;
+---------+-------------+
| u_id | u_name |
+---------+-------------+
| zhk_1 | zhp_king1 |
| king222 | zhp_king222 |
+---------+-------------+
2 rows in set (0.00 sec)
mysql>
drop table if exists c, b, a;
create table a(u_id int primary key);
create table b(id int primary key, u_id int, foreign key(u_id) references a(u_id) on update cascade);
create table c(id int primary key, u_id int, foreign key(u_id) references a(u_id) on update cascade);
insert a values(1);
insert b values(1,1);
insert c values(1,1);
select * from a, b, c;
+------+----+------+----+------+
| u_id | id | u_id | id | u_id |
+------+----+------+----+------+
| 1 | 1 | 1 | 1 | 1 |
+------+----+------+----+------+
1 row in set (0.00 sec)
update a set u_id=11;
select * from a, b, c;
+------+----+------+----+------+
| u_id | id | u_id | id | u_id |
+------+----+------+----+------+
| 11 | 1 | 11 | 1 | 11 |
+------+----+------+----+------+
1 row in set (0.00 sec)
use tempdb;
drop table if exists m, c;
create table m(
u_id int primary key, u_name varchar(10) unique
);
create table c(
id int auto_increment primary key,
u_id int, u_name varchar(10),
FOREIGN KEY (u_id) references m(u_id) on update cascade,
FOREIGN KEY (u_name) references m(u_name) on update cascade
);
insert m values(1, 'a'), (2, 'b');
insert c(u_id, u_name) values(1, 'a'), (2, 'b');
select * from m;
select * from c;
update m set u_id=u_id+10;
update m set u_name=concat(u_name, '*');
select * from m;
select * from c;
drop table if exists c, m;
use tempdb;
create table m(
u_id int primary key, u_name varchar(10) unique
);
create table c(
id int auto_increment primary key,
u_id int references m(u_id) on update cascade,
u_name varchar(10) references m(u_namae) on update cascade
);
insert m values(1, 'a'), (2, 'b');
insert c(u_id, u_name) values(1, 'a'), (2, 'b');
select * from m;
select * from c;
update m set u_id=u_id+10;
update m set u_name=concat(u_name, '*');
select * from m;
select * from c;
drop table if exists m, c;