Primary Key and foreign key in SQL Server

A primary key is used to force the uniqueness of a field or a combination of multiple fields, and the field value cannot be null. Only one primary key constraint can be set in a table, but a combination of fields containing multiple fields can be set as the primary key. Primary Key constraints include field-level constraints and table-level constraints. Field-level constraints are used to set constraints for a field. When setting a field-level constraint, you only need to add the statement that creates the primary key constraint to the definition clause of the field. Table-level constraints are used to set fields of multiple fields as primary keys.

A table can have only one primary key constraint. It is used to constrain the unique values of one or more fields and cannot be blank. The difference between it and the uniqueness constraint is that a table can have multiple uniqueness constraints and Its fields can be empty, the Uniqueness constraint is the uniqueness of the field value. This feature is consistent with that of the primary key.

The primary key constraint defines the relationship between the table and the table for the internal and external key constraints. When the combination of one or more columns in a table and the definition of the primary keyword in other tables are the same, the combination of these columns or columns can be defined as a foreign keyword, set which columns are associated with the table. A table with a primary key is usually set as a parent table, while a table with a foreign key as a parent table is also called a child table. In this way, when the column value is updated in the parent table (in the table that defines the primary key constraint), the child table (other tables with external key constraint associated with the parent table) the external keyword columns in are also updated accordingly. When data is inserted in a sub-table, if the columns in the parent table do not have the same value as the values of the inserted foreign key columns, the system rejects data insertion, that is, when the parent table updates data, the child table also updates the same data with the same constraints, when the child table updates the data associated with the parent table, if the parent table does not have the updated data, the system will refuse to insert the data, reflecting the consistency between the parent and the child, as well as the sub-parent dependency.

Example:

/* Create a database named student_info and use student_info to create the student table. s_id is the primary key to create the test table. test_no is the primary key to create the marks table. s_id and test_no in the Marks table are the external keys, map s_id in the student table and test_no */create database student_infouse student_infogocreate table student (s_id int identity () primary key, s_name varchar (20) not null, s_age INT) in the test table respectively) gocreate table test (test_no int identity (1, 1) primary key, test_name varchar (30), nax_marks int not null default (0), min_marks int not null default (0 )) gocreate table marks (s_id int not null, test_no int not null, marks int not null default (0), primary key (s_id, test_no), foreign key (s_id) references student (s_id) on update cascade on Delete cascade, foreign key (test_no) References test (test_no) on update cascade on Delete cascade) Go
 

Cascade update and cascade Delete.StudentAll scores of the student in the score table are deleted.

both the primary key and the foreign key are used to constrain data. The uniqueness of the primary key value makes it easy for the index to improve the data query speed, the foreign key is used to restrict the data between tables. The foreign key is mainly used to update and delete data in cascade mode to protect the integrity of database data. You must create a primary key before using the foreign key.