按照O'REILLY《Learning SQL》里的教学,要向原有person表的person_id列添加自增属性,但是出现了错误,新手小白起步,求教各位大神了,谢谢~
mysql> create table person
-> (person_id smallint unsigned,
-> fname varchar(20),
-> lname varchar(20),
-> gender enum('M','F'),
-> birth_date date,
-> street varchar(30),
-> city varchar(20),
-> state varchar(20),
-> country varchar(20),
-> postal_code varchar(20),
-> constraint pk_person primary key (person_id)
-> );
Query OK, 0 rows affected (0.47 sec)mysql> desc person
-> ;
+-------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| fname | varchar(20) | YES | | NULL | |
| lname | varchar(20) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| street | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| country | varchar(20) | YES | | NULL | |
| postal_code | varchar(20) | YES | | NULL | |
+-------------+----------------------+------+-----+---------+-------+
10 rows in set (0.05 sec)mysql> create table favorite_food
-> (person_id smallint unsigned,
-> food varchar(20),
-> constraint pk_person_food primary key (person_id,food),
-> constraint fk_fav_food_person_id foreign key (person_id)
-> references person (person_id)
-> );
Query OK, 0 rows affected (0.33 sec)mysql> desc favorite_food;
+-----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| food | varchar(20) | NO | PRI | NULL | |
+-----------+----------------------+------+-----+---------+-------+
2 rows in set (0.08 sec)mysql> alter table person modify person_id smallint unsigned auto_increment;
ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'bank.favorite_food'
mysql> alter table person modify person_id smallint unsigned auto_increment primary key;
ERROR 1068 (42000): Multiple primary key defined
mysql> create table person
-> (person_id smallint unsigned,
-> fname varchar(20),
-> lname varchar(20),
-> gender enum('M','F'),
-> birth_date date,
-> street varchar(30),
-> city varchar(20),
-> state varchar(20),
-> country varchar(20),
-> postal_code varchar(20),
-> constraint pk_person primary key (person_id)
-> );
Query OK, 0 rows affected (0.47 sec)mysql> desc person
-> ;
+-------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| fname | varchar(20) | YES | | NULL | |
| lname | varchar(20) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| street | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| country | varchar(20) | YES | | NULL | |
| postal_code | varchar(20) | YES | | NULL | |
+-------------+----------------------+------+-----+---------+-------+
10 rows in set (0.05 sec)mysql> create table favorite_food
-> (person_id smallint unsigned,
-> food varchar(20),
-> constraint pk_person_food primary key (person_id,food),
-> constraint fk_fav_food_person_id foreign key (person_id)
-> references person (person_id)
-> );
Query OK, 0 rows affected (0.33 sec)mysql> desc favorite_food;
+-----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| food | varchar(20) | NO | PRI | NULL | |
+-----------+----------------------+------+-----+---------+-------+
2 rows in set (0.08 sec)mysql> alter table person modify person_id smallint unsigned auto_increment;
ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'bank.favorite_food'
mysql> alter table person modify person_id smallint unsigned auto_increment primary key;
ERROR 1068 (42000): Multiple primary key defined
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货