How to write a sql foreign key statement

Foreign keys are database relationships that are used to establish associations between two tables and force data integrity. The foreign key constraint syntax in SQL is: ALTER TABLE child_table ADD FOREIGN KEY (column_name) REFERENCES parent_table(column_name). The foreign key constraint types provided by SQL include: ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL, ON

SQL Foreign Key Statement

What are foreign keys?

A foreign key is a database relationship that is used to make associations between two tables. It forces data integrity by referencing the primary key column in another table.

Foreign Key Syntax

In SQL, foreign key constraints can be created using the following syntax:

<code class="sql">ALTER TABLE child_table ADD FOREIGN KEY (column_name) REFERENCES parent_table(column_name)</code>

in:

• child_table is a table containing foreign key columns.
• column_name is a foreign key column in child_table .
• parent_table is a table containing the referenced primary key column.
• column_name is the primary key column in parent_table .

Foreign key constraint type

SQL provides the following types of foreign key constraints:

• ON DELETE CASCADE: When a record in the parent table is deleted, all records in the child table that references the record will be automatically deleted.
• ON DELETE SET NULL: When a record in the parent table is deleted, the foreign key column in the child table that references the record is NULL.
• ON DELETE SET DEFAULT: When a record in the parent table is deleted, the foreign key column in the child table that references the record is set to the default value.
• ON UPDATE CASCADE: When a record in the parent table is updated, all records in the child table that references the record will be automatically updated.
• ON UPDATE SET NULL: When a record in the parent table is updated, the foreign key column in the child table that references the record is NULL.
• ON UPDATE SET DEFAULT: When a record in the parent table is updated, the foreign key column in the child table that references the record is set to the default value.

Example

The following SQL statement creates a foreign key between orders table and customers table:

<code class="sql">ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE</code>

This means that customer_id column in orders table will reference customer_id primary key in the customers table. When a record in the customers table is deleted, all records in the orders table that reference the record will also be deleted.

The above is the detailed content of How to write a sql foreign key statement. For more information, please follow other related articles on the PHP Chinese website!