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