What Is a Foreign Key?

If the public key is the primary key in a relationship, then this public key is called the foreign key of another relationship. It can be seen that the foreign key represents the related connection between the two relationships. A table with a foreign key of another relationship as the primary key is called a master table, and a table with a foreign key is called a slave table of the master table. Foreign keys are also called foreign keywords.

Let F be one or a set of attributes of the basic relationship R, but not the key of the relationship, and Ks is the primary key of the basic relationship S. If F corresponds to Ks, then F is called the foreign key of R, and the basic relationship R is called

maintain

Foreign key example

student (s #, sname, d #), that is, the relationship between students has three attributes: student number, name, and department.

dep (d #, dname), that is, the department has two attributes: department number and department name.

Then s # and d # are the primary keys and the only candidate keys in their respective relationships, and d # is the student's foreign key.

Prerequisites for establishing a foreign key: The columns of this table must be the same as the foreign key type (the foreign key must be the foreign key of the foreign table).

Specify foreign key keywords: foreign key (column name)

Reference foreign key keywords: references <primary table name> (primary table primary key)

Event trigger restrictions: on delete and on update, can set parameters cascade (follow foreign key changes), restrict (restrict foreign key changes in foreign tables), set Null (set empty value), set Default (set default value), [default ] no action

E.g:

outTable table primary key id type int

Create a table with foreign keys:

create table temp (

id int,

name char (20),

foreign key (id) references outTable (id) on delete cascade on update cascade);

Explanation: Set the id column as the foreign key to refer to the id column of the foreign table outTable when the value of the foreign key is deleted. The corresponding column in this table is deleted. When the value of the foreign key is changed, the corresponding column value in this table is changed.

Note: It is not possible to add a trigger event of a foreign key to the created table.

create table temp (

id int,

name char (20),

foreign key (id) references outTable (id));

Foreign key usage principles

1. Create a foreign key for the associated field.

2. All keys must be unique.

3. Avoid using composite keys.

4. Foreign keys are always associated with unique key fields.

How to use foreign keys

Primary key table and external table:

When using the design interface to create a foreign key, there are problems with the primary key table and the built-in table. The above makes my understanding wrong:

CREATE TABLE TABLE1

(

[ID] INT IDENTITY (1,1) PRIMARY KEY

)

GO

CREATE TABLE TABLE2

(

[ID] INT NOT NULL,

FOREIGN KEY ([ID]) REFERENCES [TABLE1] ([ID])

)

GO

TABLE1 is referenced in TABLE2, where TABLE1 is the primary key table and TABLE2 is the foreign key table.

Summary: The primary key table is a referenced table, and the foreign key table is a table that references other tables.

Foreign key validity

Many times, programmers will find that the fields are missing, redundant, or that rows that are not constrained cannot be added after the foreign key is created [necessary in special cases]. At this time, you do not want to manipulate the table structure, you can use constraints Failure.

Take Northwind as an example: I want to add a piece of data to the Products table [Products table] that is not restricted by the Categories table [Categories table]. You can invalidate the Categories constraint in the product table.

Wording: ALTER TABLE dbo.Products NOCHECKCONSTRAINT FK_Products_Categories

Make it valid after adding:

ALTER TABLE dbo.Products CHECK CONSTRAINT FK_Products_Categories

This completes the addition of data that is not bound by a table.

There is another benefit: as in the example above. When modifying the Categories table, add all fields to invalidate all foreign keys that reference the Categories table. After adding the fields to the Categories table, you can restore the validity of all the foreign keys to the Categories table.