Foreign key constraints in the database

1. primary key constraint
A table can uniquely identify each column in the table by combining one column and multiple columns.
It is called the primary key of a table. It can be used to force Object Integrity.
When a table specifies the primary key constraint, SQL Server forces
Data uniqueness. When the primary key is used for query, the unique index can quickly access the data.

2. Foreign key constraint
A foreign key is composed of one or more columns. It is used to establish and force an association between two tables. This association is implemented
When a column or composite column in a table is added to another table, this column or composite column becomes the second
Foreign keys in tables.
A foreign key constraint does not necessarily need to be associated with the primary key constraint of another table.
It can be defined as a reference to the unique constraint in another column. The foreign key constraint can allow null values,
Is a null value in a column or combination column that forms a foreign key. The validation of the foreign key constraint is skipped. When a new table is added
When a data row or an existing data row is modified, the columns or Composite Columns defined in the foreign key must exist in the primary key of another table or
Yes. Otherwise, an error will occur.
Note: The foreign key constraint can be used to reference columns or Composite Columns in other tables in the same database.
Is a reference to other columns or Composite Columns in the table itself (self-reference table)
Although foreign keys are mainly used to restrict data that can be input to foreign key tables, they can also restrict
Data modification.
The foreign key constraint can also be used as an index because:
1. modification to the foreign key constraint or checked by the foreign key constraint in other tables
2. Foreign key columns or Composite Columns are often used in queries. For tables with foreign key constraints and other tables with primary keys or
When querying between tables with unique keys, you can use foreign key columns or Composite Columns in the connection criterion. The index allows SQL Server
Foreign key for quick search. However, the creation of such an index is not necessary.

 

A foreign key (FK) is one or more columns used to establish and enhance the link between two table data. You can create a link between two tables by adding one or more columns of the primary key values in the table to another table. This column becomes the foreign key of the second table.

When creating or changing a table, you can create a foreign key by defining the foreign key constraint.

For example, the titles table in the database pubs has a link to the publishers table because there is a logical connection between the title and the publisher. The pub_id column in the titles table corresponds to the primary key column in the publishers table. The pub_id column in the titles table is the foreign key to the publishers table.

The foreign key constraint is not only linked to the primary key constraint of another table, but can also be defined as a unique constraint that references another table. The foreign key constraint does not allow null values. However, if any combination of the foreign key constraint columns contains null values, the validation of the foreign key constraint is skipped.

The foreign key constraint can reference tables in the same database or columns in the same table (self-referenced table). For example, an employee table containing the following three columns: employee_number, employee_name, and manager _ employee_number. Because the manager is also an employee, There is a foreign key relationship between the manager_employee_number column and the employee_number column.

Although the main purpose of the foreign key constraint is to control the data stored in the foreign key table, it can also control the modification of the data in the primary key table. For example, if a publisher is deleted from the publishers table and the publisher's ID is used to record the book information in the titles table, the integrity of the association between the two tables will be damaged, in the titles table, the publisher's books are isolated because they have no links to the data in the publishers table. Foreign key constraints prevent this situation. If the data changes in the primary key table make the link to the data in the foreign key table invalid, this change cannot be implemented, thus ensuring the integrity of the reference. If you try to delete a row in the primary key table or change the primary key value, and the primary key value is related to the foreign key constraint value of the other table, this operation cannot be implemented. To successfully modify or delete the row with the foreign key constraint, You can first delete or change the foreign key data in the foreign key table, and then link the foreign key to different primary key data.