Oracle复合主键/外键问题 [英] Oracle composite primary key / foreign key question

问题描述

我在oracle的1个表中有一个复合主键.我想为第二个表中的一个表项创建一个外键，该表项引用第一个表中的复合主键.我收到错误ORA-02256.关于如何输入此内容有任何想法吗?

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

推荐答案

该错误是因为FOREIGN KEY是一列，但是您试图提供两列作为父列.无需绑定到组合键，因为restrictedgroups没有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) 
); 

我将为personid来自的任何表添加一个外键约束.

I would add a foreign key constraint for whatever table the personid would be coming from.

这篇关于Oracle复合主键/外键问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！