Day 38 – SQL FOREIGN KEY Constraint
In SQL, a FOREIGN KEY constraint is used to link two tables together based on a relationship between a column(s) in one table and the primary key column(s) in another table. The FOREIGN KEY constraint ensures that the values in the referencing column(s) in the child table exist in the referenced column(s) of the parent table or are null.
The syntax for defining a FOREIGN KEY constraint when creating a table is as follows:
CREATE TABLE table_name1 (
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype,
.....
FOREIGN KEY (column2) REFERENCES table_name2(column1)
);
In the above syntax, table_name1 is the child table, column2 is the referencing column in the child table, table_name2 is the parent table, and column1 is the referenced column in the parent table.
The syntax for adding a FOREIGN KEY constraint to an existing table is as follows:
ALTER TABLE table_name1
ADD CONSTRAINT fk_name FOREIGN KEY (column2) REFERENCES table_name2(column1);
In the above syntax, fk_name is the name of the FOREIGN KEY constraint.
It is important to maintain data integrity while creating FOREIGN KEY constraints. The referenced column(s) in the parent table must be defined as a primary key or a unique constraint. Also, the datatype and size of the referencing column(s) in the child table must match the datatype and size of the referenced column(s) in the parent table. If the FOREIGN KEY constraint is violated, the database will not allow the insertion, update, or deletion of data in the child table.
