SQL Oracle复合主键/外键问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6602099/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle composite primary key / foreign key question
提问by Christopher
I have a composite primary key in 1 table in oracle. I want to create a foreign key for one table entry in my second table that references the composite primary key in the first table. I am getting the error ORA-02256. Any thoughts on how I can enter this?
我在 oracle 的 1 个表中有一个复合主键。我想为我的第二个表中的一个表条目创建一个外键，该表项引用了第一个表中的复合主键。我收到错误 ORA-02256。关于如何进入这个的任何想法？
CREATE TABLE groupspersonx (
personid number,
groupid number,
CONSTRAINT pk_persongroupid PRIMARY KEY(personid, groupid)
);
CREATE TABLE restrictedgroups (
groupid number,
name varchar2(50),
dateadded date,
since date,
notes varchar2(1024),
CONSTRAINT pk_groupid PRIMARY KEY(groupid),
CONSTRAINT fk_persongroup FOREIGN KEY(groupid) REFERENCES groupspersonx(personid, groupid)
);
回答by OMG Ponies
The error is because the FOREIGN KEY is one column, but you're trying to supply two columns as the parent. There's no need to tie to the composite key, because the restrictedgroupsdoesn't have a personidcolumn...
错误是因为 FOREIGN KEY 是一列，但您试图提供两列作为父列。没有必要绑定到复合键，因为restrictedgroups它没有personid列......
You also have the relationship backwards - use:
你也有倒退的关系 - 使用：
CREATE TABLE restrictedgroups (
groupid number,
name varchar2(50),
dateadded date,
since date,
notes varchar2(1024),
CONSTRAINT pk_groupid PRIMARY KEY(groupid)
);
CREATE TABLE groupspersonx (
personid number,
groupid number,
CONSTRAINT pk_persongroupid PRIMARY KEY(personid, groupid),
CONSTRAINT fk_persongroup FOREIGN KEY(groupid) REFERENCES restrictedgroups(groupid)
);
I would add a foreign key constraint for whatever table the personidwould be coming from.
我会为personid将来自的任何表添加外键约束。
回答by user2178442
CREATE TABLE groupspersonx(
personid number, groupid number,
CONSTRAINT pk_persongroupid PRIMARY KEY(personid, groupid));
CREATE TABLE restrictedgroups (
pid number,
groupid number,
name varchar2(50),
dateadded date,
since date,
notes varchar2(1024),
CONSTRAINT pk_groupid PRIMARY KEY(groupid),
CONSTRAINT fk_persongroup FOREIGN KEY(pid,groupid) REFERENCES groupspersonx(personid, groupid));
*number of references columns is equals with foreign key columns
*引用列的数量等于外键列
回答by harikrish
Whenever you want to create a composite primary key or unique constraint on a column, you can't give reference in another table.
每当要在列上创建复合主键或唯一约束时，都不能在另一个表中提供引用。
for ex.
例如。
sql>create table t1( a number,b number,c number ,primary key(a,b,c));
table created.
sql>create table g1(a number constraint con_fg references t1(a));
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
Here t1 is parent table and g1 is child table. The child table can contains duplicate values in one column. So oracle will not allow that table of column.
这里 t1 是父表，g1 是子表。子表可以在一列中包含重复值。所以oracle 将不允许该列表。
See also
也可以看看
SQL>select constraint_name,constraint_type from user_constraints where table_name='T1';
CONSTRAINT_NAME C
------------------------------ -
SYS_C005822 P
So, here also the only constraint for all three columns i.e a,b,c in t1 table.
所以，这里也是所有三列的唯一约束，即 t1 表中的 a,b,c。
That's why you can't create a foreign on composite primary key or composite unique constraint
这就是为什么你不能在复合主键或复合唯一约束上创建外来约束
回答by Jay
You can't use:
你不能使用：
CONSTRAINT fk_persongroup FOREIGN KEY(groupid) REFERENCES groupspersonx(personid, groupid)
Change that too:
也改变一下：
CONSTRAINT fk_persongroup FOREIGN KEY(groupid) REFERENCES groupspersonx(groupid)
That should work.
那应该工作。
