Defining Table Relationships

In a relational database, relationships enable you to prevent redundant data from being entered. For example, when you are designing a database that tracks information about books, you might have a table named TITLES that stores information about each book, such as the book's title, date of publication, and author. You might want to store information about the author, such as the author's phone number, address, and ZIP Code. If you were to store all this information in the TITLES table, the author's phone number could be duplicated for every title that the author has written. Instead of duplicating data in a table, you could store the author information once in a separate table, AUTHOR. You could then use a key in the TITLES table that references or points to an entry in the AUTHOR table.

To ensure that your data is not out of sync, you can enforce something called referential integrity between the TITLES and AUTHOR tables. Referential integrity relationships help to ensure that information in one table matches information in another. For example, each title in the TITLES table must be associated with a specific author in the AUTHOR table. A title cannot be added to the database for an author who does not yet exist in the database. If this is the case, your application can have the user add the new author before adding the new book.

Referential integrity uses the combination of the FOREIGN KEY and PRIMARY KEY, or the UNIQUE constraint, to ensure that every dependent row in a table is correctly linked to a primary key row in another table. A table of customer numbers and orders doesn't do any good if it doesn't properly link back to another table with corresponding customer numbers and customer information. A foreign key should always hold a value equal to the value of its primary key in another table (or be NULL). Without that connection, the link is broken. If a foreign key refers to a row, the primary key for that row cannot change and the row holding the primary key cannot be deleted. Also, a foreign key cannot be added unless it has an existing primary key. In some situations, you might have an optional foreign key. In that case, the foreign key must be set to NULL.

Primary and Foreign Keys

A table usually has a column or combination of columns whose values uniquely identify each row in the table. This column (or combination) is called the primary key of the table and enforces the integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or alter a table.

A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept NULLs. Because PRIMARY KEY constraints require unique data, they are often defined as an identity column. When you specify a PRIMARY KEY constraint for a table, data uniqueness is enforced by creating a unique index for the primary key columns. This index also permits faster access to data when the primary key is used in queries. If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

A foreign key is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables when a column or combination of columns that hold one table's primary key values is added to the other table. This column becomes a foreign key in the second table. You can create a foreign key by defining a FOREIGN KEY constraint when you create or alter a table.

The table that holds the primary key is referred to as the parent table. The table holding the foreign key is known as the child table. The connection these two keep is what gives the database referential integrity.

Primary Keys

You can only have one PRIMARY KEY constraint per table. Any column listed in the PRIMARY KEY constraint must, and will, be set to NOT NULL.

The syntax for a PRIMARY KEY constraint with ALTER TABLE:

ALTER TABLE table_name
 ADD [CONSTRAINT constraint_name]
  PRIMARY KEY [CLUSTERED|NONCLUSTERED]
   { (column_name[,...n])}

Here is how to add a PRIMARY KEY constraint to an existing table. You will add my_PK_column and make it the constraint in my_table. PK_my_PK_column is the name of the constraint. We will not use [CLUSTERED|NONCLUSTERED] for now because it will be discussed in detail on Day 9, "Indexes and Performance." The default value is CLUSTERED:

ALTER TABLE my_table
 ADD my_PK_column INT IDENTITY
 CONSTRAINT PK_my_PK_column PRIMARY KEY (my_PK_column)

constraint_name can be left blank and a system-generated name will be assigned. Any error within a constraint will list the constraint_name as a reference.

Foreign Keys

A table can have up to 253 FOREIGN KEY constraints, and can reference up to 253 other tables in the FOREIGN KEY constraint. Foreign keys can be linked only to columns that are primary keys or UNIQUE constraints.

The syntax for the FOREIGN KEY constraint with ALTER TABLE is

ALTER TABLE table_name
 ADD [CONSTRAINT constraint_name]
   FOREIGN KEY { (column_name[,...n])}
     REFERENCES ref_table [(ref_column_name[,...n])]

You can have multicolumn constraints. Each column listed for the child table (foreign key) must have a corresponding column in the parent table (primary key). In the following example, the PRIMARY KEY constraint is built by combining company_ID and customer_ID. This is referenced in the FOREIGN KEY constraint as well. Listing 8.6 creates a database, a couple of tables with columns, and then sets their constraints so that you can get the "big picture."

Code Listing 8.6. Creating a Database, Tables, Columns, and Associated Constraints

 1: /* Create the database and log */
 2: USE master
 3: GO
 4: CREATE DATABASE Main
 5: ON
 6: (NAME = main_dat,
 7:  FILENAME = 'c: mssql data maindat.mdf',
 8:  SIZE = 50MB,
 9:  MAXSIZE = 200MB,
10:  FILEGROWTH = 10MB )
11: LOG ON
12: (NAME = main_log,
13:  FILENAME = 'c: mssql data mainlog.log',
14:  SIZE = 15MB,
15:  MAXSIZE = 75MB,
16:  FILEGROWTH = 5MB )
17: GO
18: /* Create the 'customers'and 'orders'tables */
19: Use main
20: GO
21: CREATE TABLE customers
22:  (company_ID int NOT NULL,
23:  customer_ID int NOT NULL,
24:  customer_name varchar(30) NOT NULL)
25: GO
26: CREATE TABLE orders
27:  (comp_ID int NOT NULL,
28:  cust_ID int NOT NULL,
29:  order_item int NOT NULL,
30:  order_descrip varchar(30) NULL,
31:  item_amount int NOT NULL DEFAULT 1)
32: GO
33: /* Set primary key */
34: Use main
35: GO
36: ALTER TABLE customers
37:  ADD CONSTRAINT PK_comp_cust_ID
38:  PRIMARY KEY (company_ID, customer_ID)
39: GO
40: /* Set foreign key and relationship to primary key */
41: Use main
42: GO
43: ALTER TABLE orders
44: ADD CONSTRAINT FK_comp_cust_ID
45:  FOREIGN KEY (comp_ID, cust_ID)
46:  REFERENCES customers (company_ID, customer_ID)
47: GO
							

You should notice in this example that the columns in the parent table and the child table have to be related, but the column names do not have to be the same in each table. The only output you will receive is

The CREATE DATABASE process is allocating 50.00 MB on disk 'main_dat'.
The CREATE DATABASE process is allocating 15.00 MB on disk 'main_log'.

ALTER TABLE table_name
  DROP CONSTRAINT constraint_name

Using Declarative Referential Integrity (DRI)

We have talked about the parent and child tables and their links with primary keys and foreign keys. This is referred to as declarative referential integrity (DRI).

SQL Server builds a clustered index for the primary key when the primary key constraint is added. Again, we'll get more into indexes on Day 9. A table can reference itself with a DRI constraint. There might be a need to have a foreign key link to a primary key with the same table. The declarative referential integrity on SQL Server allows optional or required foreign key specifications.