增多外键
增加外键约束时,假定A参照B,那么B中被参照的列必须是主键,否则语句无法执行
mysql> desc user_info;
ERROR 1146 (42S02): Table 'projectdevlop.user_info' doesn't exist
mysql> show tables;
+-------------------------+
| Tables_in_projectdevlop |
+-------------------------+
| menu_info |
| role_info |
| role_menu_info |
| user_role_info |
+-------------------------+
4 rows in set (0.00 sec)
mysql> CREATE TABLE USER_INFO(
-> id int(8) not null auto_increment,
-> userid varchar(8) primary key NOT NULL,
-> username varchar(32),
-> userpassword varchar(32),
-> userstatus varchar(2)
-> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a ke
mysql> CREATE TABLE USER_INFO(
-> id int(8) not null ,
-> userid varchar(8) primary key NOT NULL,
-> username varchar(32),
-> userpassword varchar(32),
-> userstatus varchar(2)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc user_info;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id | int(8) | NO | | NULL | |
| userid | varchar(8) | NO | PRI | NULL | |
| username | varchar(32) | YES | | NULL | |
| userpassword | varchar(32) | YES | | NULL | |
| userstatus | varchar(2) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table user_role_INFO add foreign key user_role_INFO_userid (userid) references USER_INFO(userid);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0