A foreign key constraint enforces a referential relationship between values in one table and a candidate or primary key in another. In relational schemas, it represents an association through copied key values rather than object references. section 1.2.4
Linking billing details to a user
A billing-details table can require every USERNAME to identify an existing user: CREATE TABLE BILLINGDETAILS ( ACCOUNT VARCHAR(15) NOT NULL PRIMARY KEY, BANKNAME VARCHAR(255) NOT NULL, USERNAME VARCHAR(15) NOT NULL, FOREIGN KEY (USERNAME) REFERENCES USERS(USERNAME) ); This models a many-to-one association: each billing account belongs to one user, while a user may have several accounts. section 1.2 section 1.2.4
Foreign keys shape relational representations of associations in several ways:
- Use a single foreign key for a many-to-one association, such as multiple billing accounts referencing one user. section 1.2.4
- Introduce a link table for a many-to-many association, with foreign keys referencing both participating tables. section 1.2.4
- Recognize that a foreign key targets exactly one table, so it cannot directly represent a polymorphic association spanning multiple subclass tables. section 1.2.2
CREATE TABLE USERS (
USERNAME VARCHAR(15) NOT NULL PRIMARY KEY,
ADDRESS VARCHAR(255) NOT NULL
);
CREATE TABLE BILLINGDETAILS (
ACCOUNT VARCHAR(15) NOT NULL PRIMARY KEY,
BANKNAME VARCHAR(255) NOT NULL,
USERNAME VARCHAR(15) NOT NULL,
FOREIGN KEY (USERNAME) REFERENCES USERS(USERNAME)
);
Inspect the complete USERS and BILLINGDETAILS schema, including the foreign key declaration. section 1.2
