Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-02270 Problem

840290Oct 29 2011 — edited Oct 29 2011
Hi Guys,

I'm working through the create table statements for my database but have run in to this error : 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[+

The error occurs when I'm trying to create the CONTACT table which has a composite key from PROPERTY and STAFF which are listed first:
CREATE TABLE PROPERTY
(
	PRID CHAR(8) CONSTRAINT PROPERTY_PK PRIMARY KEY,
	BRID CHAR(5) CONSTRAINT PROPERTY_FK_BRANCH REFERENCES BRANCH(BRID),
	OWNER_ID CHAR(8) CONSTRAINT PROPERTY_FK_OWNER  REFERENCES PERSON(PEID),
	TYPE CHAR(10) NOT NULL
	CONSTRAINT TYPE_CHECK CHECK (UPPER (TYPE) IN ('HOUSE', 'UNIT', 'TOWN_HOUSE', 'COMMERCIAL')),
	FIRST_LISTED DATE NOT NULL,
	STREET VARCHAR2(30) NOT NULL,
	SUBURB VARCHAR2(30) NOT NULL,
	POST_CODE CHAR(4) NOT NULL
	
);




CREATE TABLE STAFF
(
	PEID CHAR(8) CONSTRAINT FK_PEID_STAFF REFERENCES PERSON(PEID),
	BRID CHAR(5) CONSTRAINT FK_BRID_STAFF REFERENCES BRANCH(BRID),
	DOB DATE NOT NULL,
	GENDER CHAR(1) NOT NULL,
	PAYROLL NUMBER(6) NOT NULL,
	JOB VARCHAR2(50) NOT NULL,
	CONSTRAINT STAFF_PK PRIMARY KEY(PEID, BRID)
);



CREATE TABLE CONTACT
(
	PEID CHAR(8) CONSTRAINT FK_PEID_CONTACT REFERENCES STAFF(PEID),
	PRID CHAR(8) CONSTRAINT FK_PRID_CONTACT REFERENCES PROPERTY(PRID),
	CONSTRAINT CONTACT_PK PRIMARY KEY(PEID, PRID)
);
Many thanks,

Mike
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 26 2011
Added on Oct 29 2011
3 comments
332 views