SQL Server: Primary key and foreign key

1. Definition

• 1.1 What are primary keys and foreign keys
• 1.2 The role of primary and foreign keys
• 1.3 Differences between primary key, foreign key, and index

2. Primary key (primary key)

• 2.1 Setting the primary key via SSMs
• 2.2 Setting primary keys with SQL statements

3. Foreign key (foreign key)

• 3.1 Setting foreign keys via SSMs
• 3.2 Setting foreign keys with SQL statements

4.SQL statements Set primary and foreign keys to existing tables

• 4.1 Existing table Set Primary key
• 4.2 Existing Table setting foreign keys

1. Definition1.1 What are primary keys and foreign keys

There are several attributes in a record in a relational database, which can become a primary key if one of the attribute groups (note is a group) can uniquely identify a record.
Like what:
Student Table (School number, name, gender, Class)
Each student's school number is unique, the study number is a primary key

Curriculum (course number, course name, credits)
Where the course number is unique, the course number is a primary key

Score Table (School number, course number, score)
A single attribute in the score table cannot uniquely identify a record, and the combination of the study number and course number uniquely identifies a record, so the attribute group for the study number and course number is a primary key

The school number in the score table is not the primary key of the score table, but it corresponds to the student's number, and the student's table is the primary key of the student table, then the student number in the score table is the foreign key of the students ' table.

1.2 The role of primary and foreign keys

1.2.1 In order to maintain the integrity of the relational database:
A primary key is a unique identifier that determines a record, for example, a record that includes an identity plus sign, name, and age. The ID number is the only person who can identify you, others may have duplicates, so the ID number is the primary key.

A foreign key is used to associate with another table. is a field that determines the record of another table, which is used to maintain data consistency. For example, a field in a table is the primary key of table B, so he can be a foreign key to table A.

1.2.2 Play a binding role:
Foreign key value rule: A null value or a reference primary key value.
(1) When a non-null value is inserted, it cannot be inserted if the value is not in the primary key table.
(2) When updating, you cannot change the value that is not in the primary key table.
(3) When you delete a primary key table record, you can either cascade Delete or reject it when you select a foreign key record while the foreign key is being constructed.
(4) When updating a primary key record, there is also a selection of cascading updates and rejected executions.

1.3 Differences between primary key, foreign key, and index

difference	PRIMARY Key	FOREIGN Key	Index
Defined	Uniquely identifies a record, cannot be duplicated, is not allowed to be empty	The foreign key of the table is the primary key of the other table, the foreign key can have duplicate, can be a null value	The field does not have duplicate values, but can have a null value
Role	To ensure data integrity	Used to establish contact with other tables.	is to improve the speed of query sorting
Number	The primary key can only have one	A table can have more than one foreign key	A table can have multiple unique indexes

Reference Web site: The role of primary and foreign keys for SQL

2. Primary key (primary key)2.1 Setting the primary key via SSMs

2.1.1 Open the previously established table, that is, select the Database->ssmstest-> table->dbo.student Right-click dbo.student Select Design.

2.1.2 at Sno, right-click to select Set Primary key.

2.1.3 At this point can see Sno in front of a key icon, this means that Sno has been set as the primary key. Click Save or press Ctrl+s to save it.
2.1.4 Follow the steps above to open Dbo.course, right-click CNO Select Set Primary key. Save.

2.1.5 Follow the steps above to open Dbo.sc, press and hold SHIFT and click the Small box button in front of Sno and CNO to select both lines. Then right-click to select Set Primary key. Save.

2.1.6 now, the primary key setting for three tables has been completed.

2.2 Setting primary keys with SQL statements

2.2.1 Select the database->sqltest, click New Query, notice in the upper left box is sqltest, not the words manually select.

2.2.2 Enter the following code in the click New query interface:

--This is the comment information in SQL, annotated with two minus signs.DropTable Student--Delete Table studentCreateTable Student--Create TABLE student (SnoChar4)PrimaryKey--Set SNO primary key snameChar8), SageInt,ssexChar2), sdeptChar20))DropTable Course--Delete Table courseCreateTable Course--Create TABLE course (CNOChar4)PrimaryKey--Set CNO primary key CNAMEChar8), Cpnochar (4), Ccredit int) drop table SC --Delete table Sccreate table SC --CREATE TABLE SC (sno char (4), CNO char (4), Grade  int,primary key (Sno, CNO) --set Sno and CNO Property Group as primary key)        

2.2.3 Click on the above execution, or press F5 after the following screen.

2.2.4 Right sqltest Click to refresh or press F5, then select the table, right-click Select Design, you can see three tables have already set the primary key.



2.2.5 now, the primary key setting for three tables has been completed.

3. Foreign key (foreign key)3.1 setting foreign keys via SSMs

3.1.1 Select database->ssmstest-> table->dbo.sc Right-click Dbo.sc Select Design.

3.1.2 Right-click Sno or CNO to select the relationship.

3.1.3 Click Add in the lower left corner.

3.1.4 in the box after the table and column specification, you will see a button at the next three dots, and click the three-point icon.

3.1.5 because Sno in SC references sno in student, the primary key table chooses student, and then selects the corresponding Sno. The SC in the CNO because the reference is course in the SC, so there is no way to set the foreign key, you need to wait to set up, here to choose it as none. Click OK.


3.1.6 Click Add again in the lower left corner. Follow the steps above to select the primary key table as course, select the CNO in the course corresponding to the CNO in SC, and click OK.



3.1.7 Click Close, then save, and select Yes in the warning that appears.


3.1.8 Select a database->ssmstest-> the database diagram right-click Select New Database Diagram, select Yes in the pop-up window, and then add three tables, then you can see the relationship between the three tables.

3.2 Setting foreign keys with SQL statements

3.2.1 Select Database->sqltest, and then select New Query. Notice the box in the upper left corner to select Sqltest, if not, select it manually.
3.2.2 Enter the following code in the new query interface:

--This is the comment information in SQL, annotated with two minus signs.DropTable SC--Delete Table SCCreateTable SC--CREATE TABLE SC (sno char (4) foreign key references Student (Sno), --add foreign KEY constraint CNO char (4) foreign key references Course (CNO), --add foreign KEY constraint grade int,primary key (Sno, CNO)  --set Sno and CNO Property Group as the primary key)            

3.2.3 Click Execute.

3.2.4 Select a database->ssmstest-> the database diagram right-click Select New Database Diagram, select Yes in the pop-up window, and then add three tables, then you can see the relationship between the three tables.

4.SQL Statements Set Primary and foreign keys to existing tables4.1 Existing Table set Primary key

The format is:

ALTER TABLE table name
Add constraint pk_ field name--"PK" is the abbreviation for the primary key, the field name is the name of the field on which to create the main key, ' pk__ field name ' is the constraint name
Primary key (field name)--field name ibid.

Such as:

alter table studentadd constraint PK_snoprimary key (sno)

PS: If executing the above code has the following warning, stating that the table is not added to sno the non-null constraint, that is, the need to build a table when the following code is used.

Droptable student --Delete table student create table student Span class= "CO" >--CREATE TABLE Student (Sno char ( Span class= "DV" >4) not  NULL, --plus non-null constraint, without "NOT NULL" default to: Can be empty sname char (8), sage int,ssex char (2), sdept  char (20))           

4.2 Existing Table setting foreign keys

The format is:

ALTER TABLE table name
Add constraint fk_ field name--"FK" is the abbreviation for the foreign key
Foreign key (field name) references associated table name (associated field name)--note ' associated table name ' and ' Associated field name '

Such as:

alter table sc add constraint FK_sno foreign key (sno) references student(sno)

SQL Server: Primary key and foreign key