Oracle复合主键/外键问题

发布于 2024-11-18 22:25:54 字数 540 浏览 3 评论 0原文

我在 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) 
);

原文

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?

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) 
);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌化 2024-11-25 22:25:54

该错误是因为 FOREIGN KEY 是一列，但您尝试提供两列作为父列。不需要绑定到复合键，因为 restrictedgroups 没有 personid 列...

您也有向后的关系 - 使用：

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) 
);

我会添加一个personid 来自的任何表的外键约束。

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 restrictedgroups doesn't have a personid column...

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 personid would be coming from.

回复收藏 0 原文

触ぅ动初心 2024-11-25 22:25:54

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));

* 引用列的数量与外键列的数量相等

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

回复收藏 0 原文

棒棒糖 2024-11-25 22:25:54

每当您想要在列上创建复合主键或唯一约束时，您都无法在另一个表中提供引用。

例如。

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

这里t1是父表，g1是子表。子表的一列中可以包含重复值。所以oracle不会允许该表有列。

另请参见

SQL>select constraint_name,constraint_type from user_constraints where table_name='T1';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C005822                    P

因此，这里也是 t1 表中所有三列（即 a、b、c）的唯一约束。

这就是为什么您无法在复合主键或复合唯一约束上创建外部

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.