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中不行。
哪位朋友帮忙解答一下，谢谢啦！

[ACMAIN_CHM]

测试没有问题啊

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: 0

mysql> 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: 0

mysql> 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 |
+----------------+--------------------------------------------------------------

[ACMAIN_CHM]

你的MYSQL版本是多少？

mysql> select version();
+----------------------+
| 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

[爱在音乐]

我的是5.0版本

[爱在音乐]

明白了，谢谢啦！