Enforcing option (c), using a type attribute:
CREATE TABLE Computer
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType IN ('D', 'L'))
) ;
CREATE TABLE Laptop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, battery WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'L')
, FOREIGN KEY (computerType, computerID)
REFERENCES Computer (computerType, computerID)
) ;
CREATE TABLE Desktop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, monitor WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'D')
, FOREIGN KEY (computerType, computerID)
REFERENCES Computer (computerType, computerID)
) ;
and using deferred foreign key constraints on nullable columns:
CREATE TABLE Computer
( computerID INT NOT NULL
, laptopID INT NULL -- these 2 columns
, desktopID INT NULL -- are NULLable
, PRIMARY KEY (computerID)
, UNIQUE (laptopID)
, UNIQUE (desktopID)
, CHECK ( laptopID IS NOT NULL AND desktopID IS NULL -- but only one of them
OR desktopID IS NOT NULL AND laptopID IS NULL -- is NULL
)
, CHECK (laptopID = computerID) -- and the non-NULL one is
, CHECK (desktopID = computerID) -- equal to computerID
) ;
CREATE TABLE Laptop
( laptopID INT NOT NULL
, battery VARCHAR(20) NOT NULL
, PRIMARY KEY (laptopID)
, FOREIGN KEY (laptopID)
REFERENCES Computer (computerID)
) ;
CREATE TABLE Desktop
( desktopID INT NOT NULL
, monitor VARCHAR(20) NOT NULL
, PRIMARY KEY (desktopID)
, FOREIGN KEY (desktopID)
REFERENCES Computer (computerID)
) ;
ALTER TABLE Computer
ADD CONSTRAINT Laptop_Computer_FK
FOREIGN KEY (laptopID)
REFERENCES Laptop (laptopID)
DEFERRABLE INITIALLY DEFERRED
, ADD CONSTRAINT Desktop_Computer_FK
FOREIGN KEY (desktopID)
REFERENCES Desktop (desktopID)
DEFERRABLE INITIALLY DEFERRED ;
Tested in Postgres, at SQL-Fiddle
Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_ prefix.)
We remove footnote_num from:
Table: a_ref -- was named: bridge_a_reference
a_id,
ref_id
Primary key:
(a_id, ref_id)
Foreign keys:
a_id -> a
ref_id -> reference
We add this table - which will basically store only those rows from a_ref with footnote, those you want to add children into the b_ref:
Table: a_ref_with_footnote
a_id,
ref_id,
footnote_num
Primary key:
(a_id, ref_id)
Unique key:
(a_id, footnote_num)
Foreign keys:
(a_id, ref_id) -> a_ref
And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote):
Table: b_ref -- was named: bridge_b_reference:
a_id,
b_id,
ref_id,
Primary key:
(b_id, ref_id)
Foreign keys:
(a_id, b_id) -> b
(a_id, ref_id) -> a_ref_with_footnote
Best Answer
Using a third table to hold links between two independent tables is the correct method when designing a simple many-to-many relationship. You don't specifically need flag the fields as NOT NULL because they form the Primary Key and so cannot be NULL anyway.
One note, however, you've stated in your example that a Student record MUST be associated with at least one Course record, and vice versa. Your table design would not enforce this requirement, it would only allow the possibility that Student and Course records could be linked - not that they must have at least one linked record.
Your example would not enforce total participation on both sides, and I am not sure that you even could enforce it. It raises the question of which comes first, the Student or Course record given that each of those record types requires at least one link to the other record type.
Without a Student record, no Course could be created because it doesn't have a linked record, and vice versa - it's the classic "which came first" chicken or egg scenario!