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;
b int(10),
c varchar(10),
d varchar(10),
FOREIGN KEY(B,C)
REFERENCES A(B,C)
)
`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=utf8CREATE 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
-> \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,re varchar(100),
-> foreign key(myid) references tb_key1(id)
-> );
Query OK, 0 rows affected (0.13 sec)mysql>
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>
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;
-------------
如果你的这个意思是4个列作为一个复合主键,那也一样可以引用复合主键中的部分列来作位外键引用的,但要注意,必须尊重“左前缀”原则。3楼的例子里tb_key3就是引用了复合主键的部分列作为外键。
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
);