How To Reference A Composite Primary Key In Sql

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

Solution 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

Solution 2:

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

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

Test:

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

Last insert produces error ORA-02291.