Relationships best practices

Posted on 2006-07-14
Last Modified: 2008-03-03
We are moving to MSSQL.  I have all the table structures entered and primary keys setup on all tables.  I read about setting constraints, but the majority of my relationships will be one to many.  I do not want to setup a unique constraint on a FK since it will be duplicated in the table, but it relates to a PK in another table.

Small example:



In the above example, EE_ID is the PK for EMPLOYEES, EEREC_ID is the PK for EE_RECORDS, EE_ID is a FK for EE_RECORDS.  There can be many EE_RECORDS for one EMPLOYEES.  How should I setup the relationships?  What are the benefits of having these relationships?  What are the recommended practices for setting up other types of relationships?
Question by:traigo
  • 2
LVL 28

Expert Comment

ID: 17113270
>> How should I setup the relationships?

What are the benefits of having these relationships?  
A foreign key (FK) 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 by adding the column or columns that hold one table's primary key values to the other table and the value in this column will the values from the pk of other table or null cannot be other than this . This column becomes a foreign key in the second table. and the value in this column can be duplicate.

What are the recommended practices for setting up other types of relationships?
books online

Author Comment

ID: 17206387
Can you give any other information?
LVL 28

Accepted Solution

imran_fast earned 300 total points
ID: 17262866
Can you give any other information?
Like what

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. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, then verification of the FOREIGN KEY constraint will be skipped.

Note  A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table (self-referencing tables), for example, an employee table that contains three columns: employee_number, employee_name, and manager_employee_number. Because the manager is an employee too, there is a foreign key relationship from the manager_employee_number column to the employee_number column.

Although the primary purpose of a FOREIGN KEY constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. For example, if the row for a publisher is deleted from the publishers table, and the publisher's ID is used for books in the titles table, the relational integrity between the two tables is broken; the deleted publisher's books are orphaned in the titles table without a link to the data in the publishers table. A FOREIGN KEY constraint prevents this situation. The constraint enforces referential integrity by ensuring that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail if the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To change or delete a row in a FOREIGN KEY constraint successfully, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, thereby linking the foreign key to different primary key data.

A FOREIGN KEY constraint is a candidate for an index because:

Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.

Foreign key columns are often used in join criteria when the data from related tables is combined in queries by matching the column(s) in the FOREIGN KEY constraint of one table with the primary or unique key column(s) in the other table. An index allows Microsoft® SQL Server™ 2000 to find related data in the foreign key table quickly. However, creating this index is not a requirement. Data from two related tables can be combined even if no PRIMARY KEY or FOREIGN KEY constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria. For more information about using FOREIGN KEY constraints with joins, see Join Fundamentals.

Creating and Modifying FOREIGN KEY Constraints
FOREIGN KEY constraints can be:

Created when the table is created, as part of the table definition.

Added to an existing table provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraint or UNIQUE constraint in another (or the same) table. A table can contain multiple FOREIGN KEY constraints.

Modified or deleted if FOREIGN KEY constraints already exist. For example, you may want the table's FOREIGN KEY constraint to reference other columns. It is not possible to change the length of a column defined with a FOREIGN KEY constraint.

Note  To modify a FOREIGN KEY constraint using Transact-SQL or SQL-DMO, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition.

When a FOREIGN KEY constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ 2000 by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint. However, SQL Server can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column. This option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.

However, you should be careful when adding a constraint without checking existing data because this bypasses the controls in SQL Server that enforce the data integrity of the table.

Disabling FOREIGN KEY Constraints
Existing FOREIGN KEY constraints can be disabled for:

INSERT and UPDATE statements
This allows data in the table to be modified without being validated by the constraints. Disable a FOREIGN KEY constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database.

Replication processing.
Disable a FOREIGN KEY constraint during replication if the constraint is specific to the source database. When a table is replicated, the table definition and data are copied from the source database to a destination database. These two databases are usually, but not necessarily, on separate servers. If the FOREIGN KEY constraints are specific to the source database but are not disabled during replication, they may unnecessarily prevent new data from being entered in the destination database.

Delete a FOREIGN KEY constraint, thus removing the requirement, to enforce referential integrity between the foreign key columns and the related primary key (or UNIQUE constraint) columns in another table.


Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question