Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to establish a relationship between database tables and tables, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

The relationship between the database table and the table is established through the primary foreign key. If a primary key constraint is specified for a table, the database engine enforces the uniqueness of the data by automatically creating a unique index for the primary key column, while a foreign key is a combination of one or more columns used to establish and strengthen a link between data in two tables to control the data that can be stored in the foreign key table.

The relationship between the database table and the table is established through the primary foreign key.

(recommended tutorial: mysql video tutorial)

Primary key constraint

A table usually has a column or set of columns that contains values that uniquely identify each row in the table. One or more such columns are called the primary key (contention) of the table and are used to enforce the physical integrity of the table. Because primary key constraints guarantee the uniqueness of data, such constraints are often defined on identity columns.

If a primary key constraint is specified for the table, the database engine enforces the uniqueness of the data by automatically creating a unique index for the primary key column. This index also allows quick access to data when a primary key is used in a query. If a primary key constraint is defined for multiple columns, the values in one column may duplicate, but any combination of values from all columns in the primary key constraint definition must be unique.

As shown in the following figure, the ProductID and VendorID columns in the Purchasing.ProductVendor table constitute a compound primary key constraint for 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 contain only one primary key constraint.

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

Indexes generated by primary key constraints do not cause the number of indexes in the table to exceed 999 nonclustered 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 the primary key constraint must be defined as not Null. If the Null attribute is not specified, all columns participating in the primary key constraint will have the Null attribute set to not Null.

If a primary key is defined in a column of a CLR user-defined type, the implementation of that type must support binary sorting.

Foreign key constraint

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

For example, because there is a logical relationship between the sales order and the salesperson, 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 pointing to the SalesPerson table. By creating an additional key relationship, if the value of SalesPersonID does not exist in the primary key of the SalesPerson table, the value of SalesPersonID cannot be inserted into the SalesOrderHeader table.

Tables can have up to 253 other tables and columns as foreign key references (outgoing references). SQL Server 2016 (13.x) increases the limit of the number of other tables and columns (incoming references) that can be referenced in separate tables from 253 to 10000. (the compatibility level must be at least 130. The increase in the quantity limit brings the following constraints:

Only DELETE DML operations support more than 253 foreign key references. UPDATE and MERGE operations are not supported.

Tables that make foreign key references to themselves are still limited to 253 foreign key references.

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

Index of foreign key constraint

Unlike primary key constraints, creating foreign key constraints does not automatically create the corresponding index. However, it is often useful for foreign keys to create indexes manually for the following reasons:

When combining data from related tables in a query, foreign key columns are often used in join conditions by matching one or more columns in one table's foreign key constraint with primary or unique key columns in another table. Indexes enable the database engine to quickly find relevant data in foreign key tables. However, it is not necessary to create this index. Data from two related tables can be combined even if there are no primary or foreign key constraints defined on the two related tables, but the foreign key relationship between the two tables indicates that they have been optimized with their keys as conditions to be combined into the query.

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

Thank you for reading this article carefully. I hope the article "how to establish a connection between database tables and tables" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.