Must the foreign key be a primary key for another table?

is not necessarily a primary key, but must be a unique index. primary KEY constraints and uniqueness constraints are unique indexes.

FOREIGN KEY Constraint SQL Server 2008Other versions

A foreign key (FK) is one or more columns that are used to establish and enforce links between two table data. You can create a foreign key by defining the FOREIGN key constraint when you create or modify a table.

In a foreign key reference, a link is created between two tables when a table column is referenced as a column of the primary key value of another table. This column becomes the foreign key of the second table.

For example, because there is a logical relationship between sales orders and salespeople, the sales.salesorderheader table in the AdventureWorks database contains a pointer to the Sales.SalesPerson A link to a table. The salespersonid column in the salesorderheader table corresponds to the primary key column in the Salesperson table. The salespersonid column in the SalesOrderHeader table is a foreign key that points to the Salesperson table.

The FOREIGN key constraint is not only linked to a PRIMARY key constraint on another table, it can also be defined as a UNIQUE constraint referencing another table. FOREIGN key constraints can contain null values, but if any of the columns that combine FOREIGN key constraints contain null values, the validation of all values that comprise the FOREIGN key constraint is skipped. To ensure that all values of the combined FOREIGN KEY constraint are validated, specify not NULL for all participating columns.

Attention

A FOREIGN KEY constraint can refer to a column in a table in the same database or to a column in the same table. These are called "self-referencing" tables. For example, consider an employee table that contains three columns:employee_number,employee_name , and manager_employee_number. Because the manager is also an employee, there is a foreign key relationship from the manager_employee_number column to the employee_number column.

Referential integrity

Although the primary purpose of the FOREIGN key constraint is to control the data that can be stored in the Foreign key table, it can also control changes to the data in the primary key table. For example, if you delete a salesperson row in the Sales.SalesPerson table, and the sales person's ID is used by the sales order in the sales.salesorderheader table, The integrity of the associations between the two tables will be corrupted; the sales order for the salesperson that is deleted in theSalesOrderHeader table becomes orphaned because there is no link to the data in the Salesperson table.

FOREIGN KEY constraints prevent this from happening. If changes to the data in the primary key table invalidate the link to the data in the Foreign key table, the change will not be implemented, ensuring referential integrity. If you attempt to delete a row in a primary key table or change a primary key value that corresponds to a value in another table's FOREIGN key constraint, the operation will fail. To successfully change or delete a row for a FOREIGN key constraint, you must first delete or change the foreign key data in the foreign key table, which will link the external key to different primary key data. indexing the FOREIGN KEY constraint

It is often useful to create indexes for foreign keys for the following reasons:

Changes to the PRIMARY key constraint can be checked by the FOREIGN key constraint in the related table.

When you combine data from a related table in a query, you often use a foreign key column in a join condition by matching the column in the FOREIGN key constraint of one table with the primary key column or unique key column in the other table. Indexes enable the database engine to quickly find related data in the Foreign key table. However, it is not required to create this index. Even if you do not define the PRIMARY key or FOREIGN key constraint on the two related tables, you can combine the data from the two tables, but the foreign key relationship between the two tables has been optimized with its key as a condition to combine into the query. For more information about using FOREIGN KEY constraints in joins, see Join Basics and Query types and indexes. Number of FOREIGN KEY constraints in the table

SQL Server has no predefined restrictions on the number of FOREIGN key constraints that a table can contain (references to other tables), and there is no predefined limit to the number of FOREIGN key constraints owned by other tables referencing a particular table. However, the actual number of FOREIGN KEY constraints is limited by the hardware configuration and the design of the database and application. The proposed table contains no more than 253 FOREIGN key constraints, and no more than 253 key constraints referencing the table FOREIGN. You should consider the overhead of enforcing FOREIGN KEY constraints when designing databases and applications. See reference to the concept of CREATE TABLE (Transact-SQL) ALTER table (Transact-SQL) DROP table (Transact-SQL) Creating and modifying FOREIGN KEY constraint indexes