SQL | Foreign Key Constraint
A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table.
Foreign key constraint ensures referential integrity in the relation between two tables.
The table with the foreign key constraint is labeled as the child table, and the table containing the candidate key is labeled as the referenced or parent table.
Key Features of Foreign Key
1. A foreign key column can be linked to a primary key or a unique key column of the same or another table.
2. A value other than NULL is entered in the column of the foreign key constraint, that value must already exist in the referenced column of the parent table. Else you will get a foreign key violation error.
3. Foreign key constraints can only referred tables within the same database in the same server.
4. Foreign key constraints can be defined to reference another column in the same table. This is referred to as a self-reference.
5. A foreign key constraint on a single column (Column level constraint) can reference only one column in the parent table and should have the same data type as the referenced column.
6. A foreign key constraint defined at the table level (on a combination of columns) should have the same number of reference columns as the number of columns defined in the constraint list. The data type of each column in the constraint must be the same as the corresponding column in the column list.
7. There is no limit on the number of foreign key constraints a table can contain that references other tables. However, it is limited by the hardware configuration and the database design.
8. Foreign key constraints are not enforced on temporary tables.
Create Foreign Key On New Table
ON DELETE CASCADE: When we create a foreign key using the delete cascade option, it deletes the referencing columns in the child table whenever the referenced row in the parent table with the primary key is deleted.
ON UPDATE CASCADE: When a foreign key is created with the update cascade option, the referencing rows in the child table are updated whenever the referenced row in the parent table with the primary key is updated.
Create a Foreign key in an Existing Table
Now, click on the Add button to configure a new foreign key
To configure the primary key and foreign key relationship, click on the Tables and Column Specification [...] button. This will open Tables and Columns dialog box where you can select primary key and foreign key relationship.
Click Ok and the Close and then save
We can see our Save Relation in Table Columns List
