02270。00000 -“此列列表没有匹配的唯一或主键”

Question

由于某些原因，我不断地出现02270错误。不过，我不知道它有什么问题。我只知道问题在于房间表和conferencesession表。

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));

Answer 1

这是：

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));

应改为：

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));

外键需要父表中引用列的唯一或主键约束。多列约束与单列约束不可互换.