Stassi Stassi - 1 year ago 99
SQL Question

Creating multiple foreign keys in SQL oracle

/* DROP TABLE STATEMENTS*/
DROP TABLE BUILDING CASCADE CONSTRAINTS PURGE;
DROP TABLE ROOM CASCADE CONSTRAINTS PURGE;
DROP TABLE SPEAKER CASCADE CONSTRAINTS PURGE;
DROP TABLE CONFERENCESESSION CASCADE CONSTRAINTS PURGE;

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

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

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

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


In the last table, I am supposed to have one PK (SessionID) and 3 Foreign Keys(BuildingNo, RoomNo, SpeakerID), When i try to do this i get no matching primary keys??
Any help please? Thanks!!

Answer Source

It should be:

CREATE   TABLE CONFERENCESESSION
(
   SESSIONID      CHAR (4) PRIMARY KEY,
   BUILDINGNO     CHAR (2)    ,
   ROOMNO         CHAR (2)    ,
   SPEAKERID      CHAR (2)    ,
   SESSIONDATE    DATE,
   SESSIONPRICE   NUMBER (4, 2),
   CONSTRAINT fk_blding  FOREIGN KEY (BUILDINGNO) REFERENCES BUILDING (BUILDINGNO),
   CONSTRAINT fk_roomno FOREIGN KEY  (BUILDINGNO,ROOMNO)REFERENCES ROOM (BUILDINGNO,ROOMNO),
   CONSTRAINT fk_spkr_id FOREIGN KEY (SPEAKERID)  REFERENCES SPEAKER (SPEAKERID)
);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download