Database primary key and foreign key

1 What is the primary key foreign key

Student form (school number, name, gender, Class) number is a primary key

Curriculum (course number, course name, credit) course number is a primary key

The attribute group of the academic number and course number to form a primary key

The school number in the score table is not the primary key of the score table, but the primary key of the student table, the foreign key of the score table, the same course number is also the foreign key of the score table

　Definition: If the public keyword is the primary key in a relationship, then this common keyword is called the foreign key of the other relationship

A table with a foreign key of a relationship as the primary key is called the primary table, and a table with a key is called a table from the main table

2 Role of the foreign key

Foreign keys are used to maintain data consistency, integrity

The main purpose is to control the data stored in the Foreign key table

3 design principles for primary keys

1) The primary key should not be meaningful to the user

2) The primary key should be single-column to improve the efficiency of the connection and filtering operations

The use of composite keys is usually considered for two points:

A) The primary key should be meaningful-----This provides a convenient means of destroying the database

b) in a join table that describes a many-to-many relationship, you can use two foreign keys as the primary key------The table may become the other primary table from the table, and become part of the primary key from the table, so that after the table contains more columns

3) Never update the primary key

4) The primary key should not contain dynamically changing data (timestamp, etc.)

5) The primary key should be automatically generated by the computer

4 Database primary Key selection policy

When setting up a database, you need to specify a primary key for each table (a table can have only one primary key, but there may be multiple candidate indexes)

Common primary Key selection methods are:

1) Auto-grow field

Auto-Grow primary key will omit a lot of tedious work, but in data buffering mode, the value of primary key and foreign key cannot be pre-filled.

Order (orderid,orderdate)//Primary key OrderID is an auto-grow field

OrderDetail (Orderid,linenum,productid,price)

If you want to insert a record in the order table and insert several records in the OrderDetail table, in order to insert the correct OrderID field in the OrderDetail table, you must first update the order table to obtain the OrderID of the system allocation. But to ensure data consistency, the order table and the OrderDetail table must be updated at the same time under transaction protection, which is obviously contradictory

In addition, auto-grow fields can cause primary key collisions when data replication is required between multiple databases

2) Manual Growth Type field

3) Use uniqueidentifier SQL Server to provide a uniqueidentifier data type (16 bytes) and provide a build function newid () to generate a unique uniqueidentifier

4) using the comb type

The first 10 bytes of the uniqueidentifier are preserved, and the last 6 bytes represent the build time

--------------------------------------------------------------------------------------------------------------- ----------------------------------------------

1 FOREIGN key

A foreign key (FK) is one or more columns that are used to establish or strengthen a link between two table data.

By adding one or more columns of primary key values from a table to another table, you create a connection between two tables, which becomes the foreign key for the second table

The purpose of the FK constraint is to control the data stored in the exterior and to control the modification of the data in the primary key table.

For example: the information of the publisher is recorded in the publishers table, the information of the record book in the titles table, if a publisher is deleted in the table of publishers, and the ID of this publisher is used when the information of the book is recorded in the titles table, the integrity of the association between the two tables will be destroyed That the publisher's book in the titles table is orphaned because there is no link to the data in the Publisher table.

The FK constraint prevents this from happening, which is not possible if the changes in the data in the primary key table invalidate the link to the data in the foreign key table, and if you attempt to delete a row in the primary key table or attempt to modify a primary key value that is related to the FK constraint value of another table, the operation is not possible. To successfully change or delete an FK constrained row, you can now delete the foreign key data in the foreign key table or change the foreign key data, and then connect the foreign key to the different primary key data

　Foreign keys are primarily used to control the integrity of data in a database, and when manipulating data on a table, the data of one or more tables associated with it can change simultaneously　

Example:

A (b): A is the primary key, and B is a foreign key (from b.b)

B (b,c,d): B primary Key

The B field in a is either empty or a B value that exists in table B

Database primary key and foreign key