locked
Creating Constraints RRS feed

  • Question

  • How would I create a constraint on one table that depends on a value in another table? What I want to do is create a constraint that will force my company's Data Entry department to enter a certain value in a table called "Installer" if a table called "Authority" has a a certain value in one of the columns. The two tables involved are not directly related to one another.  They do have the same table as an owner.  We are using SQL Server 2005 installed on Windows Server 2003.
    Tuesday, December 29, 2009 10:46 PM

Answers

  • Hi,


    The inserts or updates using the stored procedures could be helpful if you always plan to use that SP to insert data but it does not block all the possibilities of inserting the wrong data into your table i.e. in future you might use another interface to insert data.

    So incases of such business rule enforcing on tables it is always best to use Triggers. As they give you lot flexibility to Log the users who are trying to insert bad data and ROLLBACK the INSERT. So i guess simple INSERT UPDATE AFTER trigger help you here.

    If you put a trigger on table you dont have to worry about the paths that insert is coming from i.e. as here the SP you are searching for :)

    Hope it helps

    Wednesday, December 30, 2009 5:27 AM

All replies

  • I am afraid that we can not do this in SQL Server at this moment, but I could be wrong. We do not have assertions in SQL Server yet.

    AMB

    Tuesday, December 29, 2009 10:59 PM
  • If you are using a stored procedure to insert the data, you can put the logic in the stored procedure to check the Authority table for that specific value prior to insertion in the Installer table. 
    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, December 30, 2009 12:58 AM
  • Abdshall,

    There is a Windows based GUI that Data Entry is using to build and maintain the account information that relates to these two tables.  Your suggestion gives me an idea though.  When Data Entry performs an edit and inserts data into the installer field then attempts a Save, an SQL script is executed, and it most likely is a stored procedure that is called. It seems like I need to find that stored procedure and make some modifications. 

    Lee

    Wednesday, December 30, 2009 4:14 AM
  • You can also accomplish the same in a trigger.. if you are unable to find the proc.

    ~ syi916 ~
    Wednesday, December 30, 2009 4:38 AM
  • Hi,


    The inserts or updates using the stored procedures could be helpful if you always plan to use that SP to insert data but it does not block all the possibilities of inserting the wrong data into your table i.e. in future you might use another interface to insert data.

    So incases of such business rule enforcing on tables it is always best to use Triggers. As they give you lot flexibility to Log the users who are trying to insert bad data and ROLLBACK the INSERT. So i guess simple INSERT UPDATE AFTER trigger help you here.

    If you put a trigger on table you dont have to worry about the paths that insert is coming from i.e. as here the SP you are searching for :)

    Hope it helps

    Wednesday, December 30, 2009 5:27 AM