Defining Keys

Defining primary keys, foreign keys, and "candidate" keys are an important part of normalization.

These are some of the characteristics of a normalized database:

• The columns of a table depend on the primary key of the table.
• A foreign key of a table is related to the primary or "candidate" key of another table.
• The rows in related tables are retrieved using relational joins.

For tables that will be regularly joined, it is recommended that the columns used to specify the join have the same base datatype, not including nullability or length. For example, these datatypes are considered to be the same base datatype:

• char(10) and char(20) null and varchar(30)
• int and int null

These datatypes are considered to be different base datatypes:

• int and smallint and tinyint
• char(10) and int

Note that when defining a FOREIGN KEY constraint using the REFERENCES clause of a CREATE TABLE or ALTER TABLE statement, SQL Server requires that the number of columns and the datatypes of each column in the table exactly match the columns listed in the REFERENCES clause.

For two columns of the same base datatype used to specify a join, the SQL Server optimizer can use any indexes defined on the columns to speed up the join. For example, given the following query using the pubs database:

SELECT * FROM sales, titles
WHERE sales.title_id = titles.title_id

it would be best to define the title_id column in both the sales and titles tables as the same base datatype, and to have indexes on one or both title_id columns.

SQL Server automatically creates an index for a PRIMARY KEY or UNIQUE constraint. Consider creating an index manually (using the CREATE INDEX statement) for a FOREIGN KEY constraint.

Note that because each referenced table requires some overhead, you should avoid creating redundant or overlapping FOREIGN KEY constraints. If an existing FOREIGN KEY constraint references table1..id_column:

• Another FOREIGN KEY constraint that includes a reference to table1..id_column is usually overlapping.
• Another FOREIGN KEY constraint that includes a reference to table2..id_column is usually redundant.