mysql如何对联合主键建立外键

[huxuanhui]

如题

[wwwwb]

与单键一样的

create table a1 (
b int(10),
c varchar(10),
d varchar(10),
FOREIGN KEY(B,C)
REFERENCES A(B,C)
)

[WWWWA]

CREATE TABLE `a` (
`b` int(10) NOT NULL DEFAULT '0',
`c` varchar(10) NOT NULL DEFAULT '',
`d` varchar(10) DEFAULT NULL,
PRIMARY KEY (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `a1` (
`b` int(10) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL,
`d` varchar(10) DEFAULT NULL,
KEY `b` (`b`,`c`),
CONSTRAINT `a1_ibfk_1` FOREIGN KEY (`b`, `c`) REFERENCES `a` (`b`, `c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[vinsonshen]

跟简单主键一样的，示例：

mysql> create table tb_key1(id int, itemid int, memo
    -> \c
mysql> create table tb_key1(id int, itemid int, memo varchar(20),
    -> primary key(id,itemid)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> create table tb_key2(newid int, itemid int, name varchar(50),
    -> foreign key (newid,itemid) references tb_key1(id,itemid)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> create table tb_key3(myid int,remark varchar(100),
    -> foreign key(myid) references tb_key1(id)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql>

[ACMAIN_CHM]

create table t1 (k1 int,k2 int, primary key (k1,k2));

create table t2 (
	id int primary key,
	k1 int,
	k2 int,
	foreign key (k1,k2) references t1(k1,k2)
);

insert into t1 values (1,1);

insert into t2 values (1,1,3);
insert into t2 values (1,1,1);


mysql> create table t1 (k1 int,k2 int, primary key (k1,k2));
Query OK, 0 rows affected (0.13 sec)

mysql> create table t2 (
    ->  id int primary key,
    ->  k1 int,
    ->  k2 int,
    ->  foreign key (k1,k2) references t1(k1,k2)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.09 sec)

mysql> insert into t2 values (1,1,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`csdn`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`k1`, `k2`) REFERENCES `t
1` (`k1`, `k2`))
mysql> insert into t2 values (1,1,1);
Query OK, 1 row affected (0.01 sec)

mysql>

[ACMAIN_CHM]

MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

[平凡的思想者]

CREATE TABLE product (
category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)
) TYPE=INNODB;

CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;

CREATE TABLE product_order (
no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)
) TYPE=INNODB;

[huxuanhui]

那么 如果 其中一个表中 有 四个主键 而另外一个表中有三个主键
如何建立 外键关系？

[vinsonshen]

其中一个表中 有 四个主键
一个表中有三个主键
-----------------------
一个表只能有一个主键啊
你的表现实中能这样设计？

[vinsonshen]

有 四个主键
-------------
如果你的这个意思是4个列作为一个复合主键，那也一样可以引用复合主键中的部分列来作位外键引用的，但要注意，必须尊重“左前缀”原则。

3楼的例子里tb_key3就是引用了复合主键的部分列作为外键。

[huxuanhui]

我的是联合主键

楼上的 详细解释下好么

[vinsonshen]

[Quote=引用 10 楼 huxuanhui 的回复:]
我的是联合主键

楼上的 详细解释下好么
[/Quote]

上面有详细的文字说明了，也有3楼的例子了
应该够详细了吧

[huxuanhui]

太感谢了
我搞定了
呵呵

[huxuanhui]

分数分的不均匀啊
大家见谅
给的分少了
抱歉

[墨墨酱]

CREATE TABLE USER_TBL(
UID VARCHAR(20) NOT NULL PRIMARY KEY,
UNAME VARCHAR(20) NOT NULL
);
CREATE TABLE ROLE_TBL(
RID INTEGER NOT NULL PRIMARY KEY,
RNAME VARCHAR(20) NOT NULL
);
CREATE TABLE UR_TBL(
ID INTEGER AUTO_INCREMENT not null primary key,
URUID VARCHAR(20) NOT NULL,
URRID INTEGER NOT NULL,
INDEX (URUID),
FOREIGN KEY (URUID)REFERENCES USER_TBL(UID)ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (URRID),
FOREIGN KEY (URRID)REFERENCES ROLE_TBL(RID)ON UPDATE CASCADE ON DELETE RESTRICT
);