SQL SERVER 2017 TUTORIAL : PART 07 (RELATION & JOIN)
- 6 years ago
This Sql server 2017 tutorial may help you understand relationship between tables in sql server and how to join tables. Inner join, Left inner join, left outer join, right inner join and right outer join can dramatically change query result. You have to understand Sql server 2017 joins and relation clearly to get effective query result.
Types of relation. how to create and utilize relationship.
This topic describes how to create foreign key relationships in SQL Server 2017 by using SQL Server Management Studio or Transact-SQL. You create a relationship between two tables when you want to associate rows of one table with rows of another.
Before You Begin! Limits and Restrictions
A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.
FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER (Transact-SQL).
FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference.
A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.
A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.
The Database Engine does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain that reference other tables, or the number of FOREIGN KEY constraints that are owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints that can be used is limited by the hardware configuration and by the design of the database and application. A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:
Greater than 253 foreign key references are supported for DELETE and UPDATE DML operations. MERGE operations are not supported.
A table with a foreign key reference to itself is still limited to 253 foreign key references.
Types of relation. how to create and utilize relationship.
This topic describes how to create foreign key relationships in SQL Server 2017 by using SQL Server Management Studio or Transact-SQL. You create a relationship between two tables when you want to associate rows of one table with rows of another.
Before You Begin! Limits and Restrictions
A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.
FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER (Transact-SQL).
FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference.
A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.
A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.
The Database Engine does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain that reference other tables, or the number of FOREIGN KEY constraints that are owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints that can be used is limited by the hardware configuration and by the design of the database and application. A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:
Greater than 253 foreign key references are supported for DELETE and UPDATE DML operations. MERGE operations are not supported.
A table with a foreign key reference to itself is still limited to 253 foreign key references.