locked
Referential integrity in SQL Server RRS feed

  • Question

  • Hi SQL Server Experts,

    Please help with expert guidelines to create reference tables in SQL Server.

    Thanks




    • Moved by Kalman Toth Friday, April 25, 2014 10:09 AM Not t-sql
    • Edited by Kalman Toth Friday, April 25, 2014 10:15 AM Meaning
    • Edited by recherche Saturday, April 26, 2014 10:58 AM typo
    Wednesday, April 23, 2014 3:24 AM

Answers

  • Hi,

    Referential integrity is a system of rules that ensure relationships between rows in related tables are valid and that you do not accidentally delete or change related data.

    When referential integrity is enforced, you must observe the following rules:

    • You cannot enter a value in the foreign key column of the related table if that value does not exist in the primary key of the related table. However, you can enter a null in the foreign key column. For example, you cannot indicate that a job is assigned to an employee who is not included in the employee table, but you can indicate that an employee has no assigned job by entering a null in the job_id column of the employee table.
    • You cannot delete a row from a primary key table if rows matching it exist in a related table. For example, you cannot delete a row from the jobs table if there are employees assigned to the job represented by that row in the employee table.
    • You cannot change a primary key value in the primary key table if that row has related rows. For example, you cannot delete an employee from the employee table if that employee is assigned to a job in the jobs table.

    You can set referential integrity when all of the following conditions are met:

    • The matching column from the primary table is a primary key or has a unique constraint.
    • The related columns have the same data type and size.
    • Both tables belong to the same database.

    http://msdn.microsoft.com/en-us/library/s780ea06(v=vs.71).aspx

    • Marked as answer by recherche Thursday, April 24, 2014 11:55 AM
    Wednesday, April 23, 2014 6:43 PM

All replies

  • Refer below example. ORDERS table is a reference table becouse it refer the Primary key of the PERSONS table.

    CREATE TABLE Persons
    (
     P_Id int NOT NULL PRIMARY KEY,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Address varchar(255),
     City varchar(255)
    )
    ---------------------------------------
    CREATE TABLE Orders
    (
     O_Id int NOT NULL PRIMARY KEY,
     OrderNo int NOT NULL,
     P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
    )


    Regards, RSingh

    Wednesday, April 23, 2014 3:48 AM
    Answerer
  • In addition to RSingh, if you want to refer more than one column from the Parent table then you can define a column with unique constraint in the base table.

    http://sqlknowledgebank.blogspot.com/2012/06/foreign-key-constraint-clauses.html

    http://www.w3schools.com/sql/sql_foreignkey.asp

    CREATE TABLE Persons
    (
     P_Id int NOT NULL UNIQUE,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Address varchar(255),
     City varchar(255)
    )
    ---------------------------------------
    CREATE TABLE Orders
    (
     O_Id int NOT NULL PRIMARY KEY,
     OrderNo int NOT NULL,
     P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
    )
    --Prashanth
    Wednesday, April 23, 2014 2:21 PM
  • Hi,

    Referential integrity is a system of rules that ensure relationships between rows in related tables are valid and that you do not accidentally delete or change related data.

    When referential integrity is enforced, you must observe the following rules:

    • You cannot enter a value in the foreign key column of the related table if that value does not exist in the primary key of the related table. However, you can enter a null in the foreign key column. For example, you cannot indicate that a job is assigned to an employee who is not included in the employee table, but you can indicate that an employee has no assigned job by entering a null in the job_id column of the employee table.
    • You cannot delete a row from a primary key table if rows matching it exist in a related table. For example, you cannot delete a row from the jobs table if there are employees assigned to the job represented by that row in the employee table.
    • You cannot change a primary key value in the primary key table if that row has related rows. For example, you cannot delete an employee from the employee table if that employee is assigned to a job in the jobs table.

    You can set referential integrity when all of the following conditions are met:

    • The matching column from the primary table is a primary key or has a unique constraint.
    • The related columns have the same data type and size.
    • Both tables belong to the same database.

    http://msdn.microsoft.com/en-us/library/s780ea06(v=vs.71).aspx

    • Marked as answer by recherche Thursday, April 24, 2014 11:55 AM
    Wednesday, April 23, 2014 6:43 PM
  • In addition to RSingh, if you want to refer more than one column from the Parent table then you can define a column with unique constraint in the base table.

    http://sqlknowledgebank.blogspot.com/2012/06/foreign-key-constraint-clauses.html

    http://www.w3schools.com/sql/sql_foreignkey.asp

    CREATE TABLE Persons
    (
     P_Id int NOT NULL UNIQUE,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Address varchar(255),
     City varchar(255)
    )
    ---------------------------------------
    CREATE TABLE Orders
    (
     O_Id int NOT NULL PRIMARY KEY,
     OrderNo int NOT NULL,
     P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
    )
    --Prashanth

    Hi Prashanth,

    Thank you for the reply. I was looking for something along the lines of http://technet.microsoft.com/en-us/sqlserver/bb671430.

    The plain-vanilla question is meant to be a starter.

    Also, a good video http://technet.microsoft.com/en-us/sqlserver/hh852617.aspx

    Please refer to the answer from KEAARPEE.

    Thanks again :)

    PS: Some more useful Urls

    http://www.codeproject.com/Articles/359654/important-database-designing-rules-which-I-fo

    http://blog.sqlauthority.com/2007/06/04/sql-server-database-coding-standards-and-guidelines-part-1/

    http://blog.sqlauthority.com/2007/06/05/sql-server-database-coding-standards-and-guidelines-part-2/

    http://technet.microsoft.com/en-us/library/ms187099(v=sql.105).aspx

    http://www.mssqltips.com/sql-server-tip-category/10/database-design/

    http://stackoverflow.com/questions/776031/sql-server-database-standards-best-practices

    • Edited by recherche Friday, April 25, 2014 5:11 AM typo
    Thursday, April 24, 2014 7:13 PM
  • I modified the design above to be more on the beaten path:

    CREATE TABLE Persons
    (
     P_Id int identity(1,1) PRIMARY KEY,
     Email varchar(255) UNIQUE,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Address varchar(255),
     City varchar(255),
     State char(2),
     PostalCode varchar(10),
     ModifiedDate datetime default (CURRENT_TIMESTAMP)
    )
    ---------------------------------------
    CREATE TABLE Orders
    (
     O_Id int identity(1,1) PRIMARY KEY,
     OrderNo int NOT NULL UNIQUE,
     P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
    )


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Friday, April 25, 2014 10:16 AM