0

I got a problem when adding some foreign key constraints into the 2 tables OUTPUT and SUPERVISOR:

ALTER TABLE  OUTPUT ADD CONSTRAINT PROJECT_OUTPUT_FK
FOREIGN KEY (proj_id)
REFERENCES  PROJECT (proj_id)
NOT DEFERRABLE;

ALTER TABLE  SUPERVISOR ADD CONSTRAINT PROJECT_SUPERVISOR_FK
FOREIGN KEY (proj_id)
REFERENCES  PROJECT (proj_id)
NOT DEFERRABLE;

Errors showed: 

SQL Error: ORA-02270: no matching unique or primary key for this column-list

02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

What should I do in this case?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
davehyy
  • 9
  • 5

2 Answers2

0

Make sure you have a primary key or uniquey key defined on the PROJ_ID column of table PROJECT. You can use this query for this (replace the user/owner if appropriate) and to figure out what columns are part of the PK/UK:

 select c.owner, c.constraint_name, c.constraint_type, cc.column_name
  from all_constraints c
  join all_cons_columns cc on cc.owner = c.owner and cc.constraint_name = c.constraint_name
 where c.constraint_type in ('P', 'U')
   and c.table_name = 'PROJECT'
       and c.owner = user
 order by cc.position;

If you need to create a primary key still and you have the required permissions you can do it with:

 ALTER TABLE PROJECT ADD CONSTRAINT PK_PROJECT PRIMARY KEY (proj_id);
Francisco Sitja
  • 963
  • 4
  • 7
  • But I have already set proj_id as the primary key in the PROJECT, OUTPUT, and supervisor table. Why can't they be identified？ – davehyy Jan 01 '16 at 09:23
0

Thank you guys I think I figured it out. Because the primary key in the PROJECT table is a composite key, the number of attributes does not match with the ones in SUPERVISOR and OUTPUT, so the error pops up.

davehyy
  • 9
  • 5