How to reference a composite primary key in SQL

Question

I have created the following 3 tables using the following code.

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),
CONSTRAINT ROOM_FK1 FOREIGN KEY(BUILDINGNO) REFERENCES BUILDING(BUILDINGNO));

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

The 4th table i need to create is something like this.

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

However I know that the constraint for the FK2 ROOMNO (2nd last line) is incorrect as the PK in the ROOM table is BUILDINGNO,ROOMNO. What would be the correct code to use for this line?

Answer 1

We declare an SQL FK (FOREIGN KEY) constraint to say that a subrow value for a list of columns always appears elsewhere as a subrow value for a list of columns that forms an SQL PK (PRIMARY KEY) or UNIQUE NOT NULL. Declare it whenever it isn't already implied by other declarations. It must reference the column list in a declared SQL PK (PRIMARY KEY) or UNIQUE NOT NULL. So you must declare that in the referenced table, even if that's already implied by NOT NULLs and a smaller contained PK or UNIQUE NOT NULL.

So note that an SQL PK is not necessarily a PK in the relational sense of being unique but not containing a smaller unique column set, ie being a superkey not containing a smaller superkey, ie being a minimal/irreducible superkey, ie being a CK (candidate key).

Here, you might need to replace the buildingno & roomno FKs by one, (buildingno, roomno) to Room:

CONSTRAINT SESSION_FK12
    FOREIGN KEY(BUILDINGNO,ROOMNO) REFERENCES ROOM(BUILDINGNO,ROOMNO)

That might be appropriate for the meanings of your tables--which in fact you don't give, so we can't know, we can only guess. Eg if buildingno could also be declared PK or UNIQUE NOT NULL in Room, which when roomno IS NOT NULL is actually consistent with and implies (buildingno, roomno) could be declared PK or UNIQUE NOT NULL, maybe your FK is right but your Room declarations are inadequate.

When a subrow value for a list of columns always appears elsewhere as a subrow value for a list of columns that is called an IND (inclusion dependency) constraint. There's no way to declare a non-FK IND in SQL; we must enforce by triggers. That also might be what you need for your design.

You could keep the FK from buildingno to Building, but it's implied by the FK I suggest and the FK in buildingno on Room referencing Building.

referencing part of the composite primary key

Answer 2

As we can see in documentation we can create composite foreign key:

CREATE TABLE CONFERENCESESSION ...
...
CONSTRAINT SESSION_FK2 
    FOREIGN KEY(BUILDINGNO, ROOMNO) 
    REFERENCES ROOM(BUILDINGNO, ROOMNO),
...

Test:

insert into building values (1, null, null);
insert into room values (1, 1, null);
insert into speaker (speakerid) values (1);
insert into conferencesession (sessionid,buildingno,roomno,speakerid) values (1, 1, 1, 1);
insert into conferencesession (sessionid,buildingno,roomno,speakerid) values (2, 1, 2, 1);

Last insert produces error ORA-02291.