SQL Reference - REFERENCES

Example

CREATE TABLE Authors(
    AuthorID INT,
    AuthorName VARCHAR(100),
    PRIMARY KEY (AuthorID)
);

CREATE TABLE Books(
    BookID INT,
    BookName VARCHAR(100),
    AuthorID INT,
    PRIMARY KEY (BookID),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

Output

Query OK, 0 rows affected (0.20 sec)

Query OK, 0 rows affected (0.12 sec)

Explanation

The SQL above creates two tables: Authors and Books. The Authors table has two columns (AuthorID and AuthorName) with AuthorID as the primary key. The Books table has three columns (BookID, BookName, and AuthorID) with BookID as the primary key. The AuthorID in the Books table is declared as a foreign key that references the AuthorID in the Authors table. This establishes a relationship between the two tables, ensuring that every book has a corresponding author in the Authors table.

Example

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_id integer REFERENCES products (product_id),
    quantity integer
);

Output

CREATE TABLE

Explanation

In PostgreSQL, the REFERENCES keyword is used to enforce a foreign key relationship. In the code example, product_id field in the orders table is set as a foreign key that refers to the product_id field in the products table. If any value is entered into the product_id field of the orders table that doesn’t exist in the product_id field of the products table, a foreign key constraint violation error will be triggered. The output message “CREATE TABLE” signifies that the orders table was created successfully.

Example

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE Customers (
    CustomerID int NOT NULL,
    CustomerName varchar(255),
    PRIMARY KEY (CustomerID)
);

Output

Commands completed successfully.

Explanation

In the example above, a foreign key constraint is added to the Orders table. The CustomerID in the Orders table references CustomerID in the Customers table. Therefore, for each order, it should have a correlating customer. This constraint ensures referential integrity in the database.

Example

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Output

Table ORDERS created.

Explanation

In the above example, a new table named Orders is created with three columns: OrderID, OrderNumber, and CustomerID. The OrderID column is assigned as the primary key of this table, and the CustomerID column is defined as a foreign key that references CustomerID in the Customers table. This ensures the referential integrity between the Orders table and the Customers table, meaning that for each row in the Orders table, the value of CustomerID must match a value in the Customers table. If a matching value doesn’t exist or is deleted in the Customers table, SQL will prevent the operation to maintain data integrity.

Example

CREATE TABLE Customers (
    ID INT PRIMARY KEY,
    Name TEXT NOT NULL
);
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Product TEXT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);
INSERT INTO Customers (ID, Name)
VALUES (1, 'John Doe');

INSERT INTO Orders (OrderID, CustomerID, Product)
VALUES (1, 1, 'Apples');

Output

Tables created

Explanation

In the example, two tables are created: Customers and Orders. The Orders table has a foreign key (CustomerID) that references the primary key (ID) of the Customers table. This helps to maintain relational data consistency, whereby an order can only be related to an existing customer. The INSERT INTO statements are adding a sample customer and order in their respective tables. The example showcases how to use REFERENCES in SQL, particularly SQLite.