/*这是个登录表*/
drop table if exists login_tb ;
create table login_tb
(
ID int primary key AUTO_INCREMENT,
username varchar(50) unique,
password varchar(50),
state int default 1 check(state=0 or state=1)
);
/*这是消息表*/
drop table if exists information_tb;
create table information_tb
(
id int primary key AUTO_INCREMENT,
title varchar(50),
content varchar(300),
sender_name varchar(50),
receiver_name varchar(50),
send_time date,
state int default 1 check(state=0 or state=1)
);
/*在发送者和接受者上建立外键约束,参照自登录表,当建立第一个外键时,创建成功,但到第二个时,出现duplicate key name ...的警告,不能创建成功,为什么?难道在mysql中不能在同一个表中的两个列(如sender_name,receiver_name)创建引用自另一个表的同一列的外键?*/
alter table information_tb add constraint pk_inf_login1 foreign key information_tb(sender_name) references login_tb(username);
alter table information_tb add constraint pk_inf_login2 foreign key information_tb(receiver_name) references login_tb(username);同样的情况放在mssqlserver中或oracle中都可以,就是在mysql中不行。
哪位朋友帮忙解答一下,谢谢啦!
drop table if exists login_tb ;
create table login_tb
(
ID int primary key AUTO_INCREMENT,
username varchar(50) unique,
password varchar(50),
state int default 1 check(state=0 or state=1)
);
/*这是消息表*/
drop table if exists information_tb;
create table information_tb
(
id int primary key AUTO_INCREMENT,
title varchar(50),
content varchar(300),
sender_name varchar(50),
receiver_name varchar(50),
send_time date,
state int default 1 check(state=0 or state=1)
);
/*在发送者和接受者上建立外键约束,参照自登录表,当建立第一个外键时,创建成功,但到第二个时,出现duplicate key name ...的警告,不能创建成功,为什么?难道在mysql中不能在同一个表中的两个列(如sender_name,receiver_name)创建引用自另一个表的同一列的外键?*/
alter table information_tb add constraint pk_inf_login1 foreign key information_tb(sender_name) references login_tb(username);
alter table information_tb add constraint pk_inf_login2 foreign key information_tb(receiver_name) references login_tb(username);同样的情况放在mssqlserver中或oracle中都可以,就是在mysql中不行。
哪位朋友帮忙解答一下,谢谢啦!
mysql> create table login_tb
-> (
-> ID int primary key AUTO_INCREMENT,
-> username varchar(50) unique,
-> password varchar(50),
-> state int default 1 check(state=0 or state=1)
-> );
Query OK, 0 rows affected (0.13 sec)mysql> create table information_tb
-> (
-> id int primary key AUTO_INCREMENT,
-> title varchar(50),
-> content varchar(300),
-> sender_name varchar(50),
-> receiver_name varchar(50),
-> send_time date,
-> state int default 1 check(state=0 or state=1)
-> );
Query OK, 0 rows affected (0.08 sec)mysql> alter table information_tb add constraint pk_inf_login1 foreign key infor
mation_tb(sender_name) references login_tb(username);
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table information_tb add constraint pk_inf_login2 foreign key infor
mation_tb(receiver_name) references login_tb(username);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show create table information_tb;
+----------------+--------------------------------------------------------------
| Table | Create Table
+----------------+--------------------------------------------------------------
| information_tb | CREATE TABLE `information_tb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(50) DEFAULT NULL,
`content` varchar(300) DEFAULT NULL,
`sender_name` varchar(50) DEFAULT NULL,
`receiver_name` varchar(50) DEFAULT NULL,
`send_time` date DEFAULT NULL,
`state` int(11) DEFAULT '1',
PRIMARY KEY (`id`),
KEY `pk_inf_login1` (`sender_name`),
KEY `pk_inf_login2` (`receiver_name`),
CONSTRAINT `pk_inf_login2` FOREIGN KEY (`receiver_name`) REFERENCES `login_tb`
(`username`),
CONSTRAINT `pk_inf_login1` FOREIGN KEY (`sender_name`) REFERENCES `login_tb` (
`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------------+--------------------------------------------------------------
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)mysql>当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html