0

For some reason I keep coming up with the 02270 error. However I'm not sure what's wrong with it. All I know is that the problem lies with the room table and the conferencesession table.

CREATE TABLE BUILDING (
BUILDINGNO CHAR(2),
BUILDINGWING VARCHAR2(15),
BUILDINGLANE VARCHAR2(15),
CONSTRAINT BUILDING_PK PRIMARY KEY(BUILDINGNO));

CREATE TABLE SPEAKER (
SPEAKERID CHAR(2),
SPEAKERNAME VARCHAR2(20),
SPEAKERADDRESS VARCHAR2(50),
SPEAKERPHONE NUMBER(12),
CONSTRAINT SPEAKER_PK PRIMARY KEY (SPEAKERID));

CREATE TABLE ROOM (
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
ROOMCAPACITY NUMBER(3),
CONSTRAINT ROOM_PK PRIMARY KEY(ROOMNO,BUILDINGNO),
CONSTRAINT ROOM_FK1 FOREIGN KEY(BUILDINGNO)REFERENCES BUILDING(BUILDINGNO));

CREATE TABLE CONFERENCESESSION (
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT CONFERENCESESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCESESSION_FK1 FOREIGN KEY(BUILDINGNO) REFERENCES 
BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCESESSION_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCESESSION_FK3 FOREIGN KEY(SPEAKERID) REFERENCES 
SPEAKER(SPEAKERID));

1 Answer 1

1

This:

CREATE TABLE CONFERENCESESSION (
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT CONFERENCESESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCESESSION_FK1 FOREIGN KEY(BUILDINGNO) REFERENCES 
BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCESESSION_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCESESSION_FK3 FOREIGN KEY(SPEAKERID) REFERENCES 
SPEAKER(SPEAKERID));

Should be changed to:

CREATE TABLE CONFERENCESESSION (
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT CONFERENCESESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCESESSION_FK1 FOREIGN KEY(BUILDINGNO, ROOMNO) REFERENCES 
ROOM(BUILDINGNO, ROOMNO),
CONSTRAINT CONFERENCESESSION_FK2 FOREIGN KEY(SPEAKERID) REFERENCES 
SPEAKER(SPEAKERID));

A foreign key requires a unique or primary key constraint on the referenced columns in the parent table. A multi-column constraint is not interchangable with single-column constraints.

1
  • @ypercubeᵀᴹ Indeed, fixed. It works with mixed order as well. Sep 27, 2017 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.