Foreign Key vs. Trigger Referential Integrity in SQL Server

By:   |   Updated: 2008-05-29   |   Comments (7)   |   Related: 1 | 2 | 3 | 4 | 5 | > Referential Integrity


Problem

I'm designing a table and I want to enforce a relationship between two tables (a parent table and a child table). Would I be better off just writing triggers to enforce the relationship or should I declare a foreign key constraint?  What are the pros and cons of each  approach?  Can you provide some sample code to show the various issues?

Solution

I have come across a lot of database models where the original developer had enforced basic referential integrity (RI) using triggers rather than declaratively via foreign keys. In versions prior to SQL Server 2000, triggers were one way to implement cascading deletes and updates of child tables since SQL Server versions prior to 2000 did not have this feature. However, SQL Server 2000 introduced cascading actions to foreign keys (ON DELETE CASCADE, ON UPDATE CASCADE) rendering the use of triggers for RI largely unnecessary except under specialized circumstances.

The following table examines some of the considerations in using either method:

    Enforcing RI: Foreign Keys vs Triggers
    Foreign Keys Triggers
    Checked before the data modification is made Executed after the data modification has been made
    Do not extend transactions Extend the life of a transaction as it executes code to check RI (can be extended even longer if an integrity violation is encountered which requires ROLLBACK)
    Can be added with an ALTER TABLE statement; No special coding required Coding is required
    Prevent TRUNCATE of a parent table Do not prevent TRUNCATE of a parent table; DELETE triggers are not fired when a TRUNCATE is issued
    Table relationships can be easily seen in data model diagrams Table relationships are not readily seen in data model diagrams
     

    In the following example, we'll examine some of the drawbacks of using triggers to enforce data integrity. The following example data model has a parent table called saved_report and child table called saved_report_parameters. Using these tables, users are able to store custom copies of an application's canned reports along with the parameters he/she used to produce a custom report. We'll also create a delete trigger maintaining the relationship between them. In our example application, we do not cascade deletes through child rows; Child rows are maintained in a separate UI.

    In a Management Studio connection, run the following script:


    -- create a parent table
    create table dbo.saved_report
    (
    saved_report_id int identity(1,1) not null primary key,
    saved_name nvarchar(40) not null unique,
    )
    go

    insert into dbo.saved_report (saved_name) select 'Customer Acme Sales Report - 2007'
    go

    -- create a the child table
    create table dbo.saved_report_parameters
    (
    saved_report_parameters_id int identity(1,1) not null primary key,
    saved_report_id int not null,
    parameter_name nvarchar(20) not null,
    parameter_value nvarchar(20) not null,
    constraint uq_saved_report_parameter unique (saved_report_id, parameter_name, parameter_value)
    )
    go

    insert into dbo.saved_report_parameters (saved_report_id, parameter_name, parameter_value)
    select 1, 'customerid', '39'
    insert into dbo.saved_report_parameters (saved_report_id, parameter_name, parameter_value)
    select 1, 'asofdate', '2007-12-31'
    go

    -- create a trigger to enforce the parent/child relationship if the parent is deleted
    create trigger dbo.tr_saved_report_delete on dbo.saved_report
    for delete
    as

    set nocount on

    if exists (select 1 from dbo.saved_report_parameters s
            join deleted d on d.saved_report_id = s.saved_report_id)
    begin
        raiserror ('Delete not allowed; child rows exist', 16, 10)
        waitfor delay '00:00:10' -- delay a bit to allow the showing of held locks in a separate connection
        rollback
    end
    go
     

    The application allows the user to delete saved reports via a UI which calls a stored procedure. However, any child rows must be deleted via a separate UI before the parent can be deleted. Run the following command in a new Management Studio connection to delete all saved reports:
     

    delete from dbo.saved_report
    go

    In a separate Management Studio connection, run the following command while the delete is running:


    select resource_type,
            resource_associated_entity_id,
            request_mode, request_type
    from sys.dm_tran_locks
    go

    You'll see that the trigger is part of the transaction as illustrated by the held locks.

    fig 1
     
    When the trigger completes, you'll see the following message in the results pane of the delete query
     
    fig 2
    What if the stored procedure was coded to perform a TRUNCATE as opposed to a DELETE if the user chooses to delete all saved reports? Well, when performing wholesale purging of all of a table's rows, a TRUNCATE can be faster since this operation is only minimally logged (only page deallocations are recorded in the transaction log). Let's issue a TRUNCATE.
     

    truncate table dbo.saved_report
    go

    After the TRUNCATE completes, we'll examine the parent and child tables

    select * from dbo.saved_report
    select * from dbo.saved_report_parameters
    go
     
    fig 3

    Since DELETE triggers are not fired when a TRUNCATE is issued, we've just orphaned the child rows.

    If you re-create the tables without the trigger and declare the following foreign key, you will see that your data is protected from a stray TRUNCATE.

    alter table dbo.saved_report_parameters
    add constraint fk_saved_report_parameters
    foreign key (saved_report_id)
    references dbo.saved_report(saved_report_id)
    go
     

    truncate table dbo.saved_report
    go

     

    fig 4
     

    I believe in using foreign keys over triggers when enforcing basic referential integrity. I am very diligent about using declarative constraints in my data models for good reasons:

    • I don't have the time to code and maintain triggers or stored procedures to handle database integrity for every table I add to my model
    • I don't have the time to create repair scripts if a bug is exposed in one of these routines.
    • I also want to keep my transactions short so I don't end up in blocking situations.
    • Furthermore, foreign keys and the other constraint options (i.e. CHECK, UNIQUE, PRIMARY KEY) are the database's last line of defense in the prevention of orphaned and invalid data due to coding errors in the logic tier as well as the database tier.
    Next Steps


    sql server categories

    sql server webinars

    subscribe to mssqltips

    sql server tutorials

    sql server white papers

    next tip



    About the author
    MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

    This author pledges the content of this article is based on professional experience and not AI generated.

    View all my tips


    Article Last Updated: 2008-05-29

    Comments For This Article




    Tuesday, January 19, 2010 - 11:05:55 PM - aprato Back To Top (4739)

     <<Aha, that was exactly how I planned it at the beginning, however it didn't work out because an order can have multiple products attached to it.>>

    That's where a LineItem table comes into play.  In the classic sense, a line item represents an ordered product in a customer's order

    Think of it this way.   Think of the nouns that you need for your model by forming
    a statement about what you're trying to solve:

    A customer can order one or many products 

    You would need:

    A customer
    A product
    An order
    A line item (to represent more than 1 product per order)

    Now think about how they relate to each other.  This would determine your FK constraints.

    Can a customer exist without a product?  Yes, so there's no relation

    But...

    Can an order exist without a customer?  No.  So customer_id would be a FK in the Orders table.

    When you get into transactions, you'd need to be aware that triggers can extend the life of the transaction which could cause a condition called blocking where readers can't get data they need becuase someone is writing data and vice versa.  They have their place but they're not my choice for data checking.


     

    Tuesday, January 19, 2010 - 6:44:03 PM - DreVinci Back To Top (4738)

    Aha, that was exactly how I planned it at the beginning, however it didn't work out because an order can have multiple products attached to it.  Thus, I'd need a bunch of optional foreign key product fields? Lol...(And I only got rid of my Orders table because my programming teacher's motto is "Simple, simple simple" hence she told me to get rid of what she thought was redundant data in the Orders table >_<)  Lol.

    But now that I think about it....Instead I can bring back the Orders table, and have a reference to the Customer (like you said).  But instead of referencing the Products in the Orders, I'll reference the Orders in the Products.  This way, we'll have a Customer and his info.  Then the Order will reference the Customer_ID and have w/e info.  And each Product will have a reference to that Order_ID.  Oh snap!  Might that work?

    Noooo...Because this way I'll still be able to input a value into product.order_id for an Order that hasn't been created yet.....My goal is to have some type of constraint that will stop me from inserting a foreign key value that has no matching primary key already in the database.  Basically, if the primary key is not in the database, you can't enter this foreign key.  So, I went full circle.  Is this possible with triggers?

    Lol, thanks for the help and the recommendation towards AdventureWorks, although I've got almost 400records plus some old stuff from Oracle I can use...and I apologize for being so confusing.  ^_^  Perhaps I'm missing something, and the way you explained it was correct....

    Tuesday, January 19, 2010 - 5:06:11 PM - aprato Back To Top (4737)

    Hi... it's nice to see such a young person interested in databases.  I think you'll find it to be a rewarding endeavor.

     In your case, I think what you're looking for is 3 tables

    1. a Customer table
    2. a Product table
    3. an Orders table (and probably a Line_Item table)

     You would have a PK on each table (i.e. Customer_id, Product_id, and Order_id)

     The Orders table would contain the Customer_id and Product_id in addition to Order_id  (along with any other columns you'd need).   You would add a FK for the Customer_id and Product_id which would, in effect, say that "You can't have an order unless (a) you have a valid customer and a (b) a valid product".

     I recommend you download the AdventureWorks sample database from CodePlex where you can examine a sample Orders schema that contains sample data that you can experiment with.

    Let me know if you're not clear and I can try to work it through a bit more with you.


    Tuesday, January 19, 2010 - 2:08:09 PM - DreVinci Back To Top (4734)

    Thanks for the article.  I have one question though:

    At the moment, I have a relationship between two tables product and customer and they're linked by an order_id column in each table and using a foreign key relationship.  However, when inputting data into the product table I want it to make sure that the order_id in the customer table already exists, meaning that you won't be able to enter product until the parent order_id in the customer is created.

    To clear it up some...
    Customer table has primary key "order_id".
    Product table has foreign key "order_id".
    I want to make sure that when you enter the foreign key, there must be a matching primary key in customer.

    Is this possible?  If so, would this be done using a trigger?  If so, how would I go about doing that?  As I'm clueless with pl/sql at the moment.  (In 10th grade, we dont start pl/sql until next semester D=)

    Tuesday, September 29, 2009 - 6:41:42 AM - mrsocks Back To Top (4106)

     Thanks for the reply.

    Yeah, that was what I was thinking about after writting this question out. I thought there was a reason our application needed it to be done that way, but we'll see. I have been testing out some things doing it this way since i posted this yesterday.

    Thanks again.


    Monday, September 28, 2009 - 5:46:55 PM - aprato Back To Top (4102)

    Rather than store an active flag in the child tables, just store it in the parent table and let the parent dictate if the titles are active.  That way, you don't need a trigger and you don't have any db overhead in maintaining the child table entries.  The way you described it, it sounds like either the titles are all active or all inactive depending on the status of the parent (author).

     

    Monday, September 28, 2009 - 1:52:12 PM - mrsocks Back To Top (4101)

     Thank you for the article.

    I am trying to find the best way to maintain some info across a number of child tables by a value in a parent table. 

    Table structures are as such:

    (parent)

    Author_Info
    |id|name|active|
    |1|Poe|1|
    |2|Roth||1|
    |3|Twain|0|

    (child)

    Books
    |id|author_id|title|active|
    |1|1|Tell tale heart|1|
    |2|1|The Pit and the Pendulum|1|
    |3|2|Cabin Fever|1|
    |4|2|Hostel|1|
    |5|3|Tom Swayer|0|
    |6|3|Huck Finn|0|

    Currently, I have a trigger that when a author's active column is changed, it updates the 'active' column in the all the records in the 'Books' table  that match that authro's id.

    I am chaning this to use a multi-column FK on (author_id, active) against (id, active) of the Author_Info table that ON DELETE No Action, ON UPDATE Casecade. Is this a better solution?

     

    Thanks.















    get free sql tips
    agree to terms