Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle composite primary key / foreign key question

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) 
); 
like image 646
Christopher Avatar asked Jul 06 '11 19:07

Christopher


2 Answers

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.

like image 176
OMG Ponies Avatar answered Oct 03 '22 00:10

OMG Ponies


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

like image 26
user2178442 Avatar answered Oct 03 '22 00:10

user2178442