How to establish relationships between database tables

The database tables are connected through primary and foreign keys. If a primary key constraint is specified for a table, the database engine will enforce the uniqueness of the data by automatically creating a unique index on the primary key column; a foreign key is one or more columns used to establish and strengthen a link between data in two tables A combination of to control the data that can be stored in the foreign key table.

The operating environment of this tutorial: Windows 7 system, mysql version 5.8, Dell G3 computer.

The database tables are connected through primary and foreign keys.

(Recommended tutorial: mysql video tutorial)

Primary key constraint

Table usually has a unique identification table A column or set of columns of values ​​for each row in . Such a column or columns is called the table's primary key (PK) and is used to enforce the table's entity integrity. Because primary key constraints ensure the uniqueness of data, they are often defined on identity columns.

If a primary key constraint is specified for a table, the database engine will automatically create a unique index on the primary key column to enforce the uniqueness of the data. This index also allows fast access to data when the primary key is used in queries. If a primary key constraint is defined on multiple columns, values ​​in a column may be repeated, but any combination of values ​​from all columns in the primary key constraint definition must be unique.

As shown in the figure below, the ProductID and VendorID columns in the Purchasing.ProductVendor table constitute a composite primary key constraint on this table. This ensures that each row in the ProductVendor table has a unique combination of ProductID and VendorID. This prevents duplicate rows from being inserted.

• #A table can only contain one primary key constraint.

• The primary key cannot exceed 16 columns and the total key length cannot exceed 900 bytes.

• Indices generated by primary key constraints will not increase the number of indexes in the table beyond 999 non-clustered indexes and 1 clustered index.

• If no clustered or nonclustered index is specified for the primary key constraint, and there is no clustered index in the table, a clustered index is used.

• All columns defined in a primary key constraint must be defined not to be Null. If nullability is not specified, the nullability of all columns participating in the primary key constraint will be set to not null.

• If you define a primary key on a column of a CLR user-defined type, the implementation of that type must support binary sorting.

Foreign key constraints

A foreign key (FK) is a column used to establish and strengthen a link between data in two tables Or a combination of columns that controls the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when one or more columns that contain primary key values ​​from one table are referenced by one or more columns in another table. This column becomes the foreign key to the second table.

For example, because there is a logical relationship between sales orders and salespersons, the Sales.SalesOrderHeader table contains a foreign key link to the Sales.SalesPerson 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 to the SalesPerson table. By creating a foreign key relationship, the value for SalesPersonID cannot be inserted into the SalesOrderHeader table if the value for SalesPersonID does not exist in the primary key of the SalesPerson table.

A table can reference up to 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x) increases the limit on the number of other tables and columns that can be referenced in a separate table (incoming references) from 253 to 10,000. (Compatibility level must be at least 130.) Increasing the number limit imposes the following constraints:

• Only the DELETE DML operation supports more than 253 foreign key references. UPDATE and MERGE operations are not supported.

• A table that has foreign key references to itself can still only have 253 foreign key references.

• Columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables do not currently support more than 253 foreign key references.

Index for foreign key constraints

Unlike primary key constraints, creating a foreign key constraint will not automatically create a corresponding index. But it is often useful to manually create indexes on foreign keys for the following reasons:

• Foreign key columns are often used in join conditions when combining data from related tables in a query, method Matches one or more columns in a foreign key constraint in one table to a primary key column or unique key column in another table. Indexes enable the database engine to quickly find related data in foreign key tables. However, creating this index is not required. Data from two related tables can be combined even if no primary key or foreign key constraints are defined on the two tables, but a foreign key relationship between two tables indicates that it has been optimized using their keys as conditions. to combine into queries.

• Changes to primary key constraints can be checked by foreign key constraints in related tables.

For more programming-related knowledge, please visit: Programming Video! !

The above is the detailed content of How to establish relationships between database tables. For more information, please follow other related articles on the PHP Chinese website!