Solved

Subquery in Check constraint

Posted on 2001-08-29
6
2,012 Views
Last Modified: 2008-02-26
SQL Server 7.0 is telling me that I can't do this:

CONSTRAINT my_check CHECK (ColumnName IN (SELECT SomeValue FROM ReferenceTable))

It tells me that subqueries are not allowed.  So then, how do I enforce this business rule at the database level?
0
Comment
Question by:stino
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:ibro
ID: 6438465
CONSTRAINT myconstraint
  FOREIGN KEY  
            REFERENCES ReferenceTable ( SomeValue)  
           
0
 
LVL 3

Expert Comment

by:ibro
ID: 6438476
sorry...i missed something

CONSTRAINT myconstraint
 FOREIGN KEY  (MyValue)
           REFERENCES ReferenceTable ( SomeValue)  
0
 
LVL 1

Author Comment

by:stino
ID: 6438596
cool, but supposed I wanted to do a filter. Eg.  CONSTRAINT myConstraint CHECK (SELECT SomeField FROM SomeTable WHERE SomeField = 'CONSTANTVALUE')

your solution basically means that I'm enforcing referential integrity on the two tables.  Which will work fine for what I'm doing now but I foresee the need to accomplish the above mentioned scenario
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 30 total points
ID: 6439375
In that case, you MUST use triggers to enforce these rules:

CREATE TRIGGER <triggername>
ON YourTable
FOR INSERT, UPDATE
IF UPDATE(ColumnName)
  IF EXISTS ( SELECT ColumnName FROM inserted LEFT JOIN SomeTable ON SomeTable.SomeField = inserted.ColumnName
  WHERE <whatever>  
   AND SomeTable.SomeField IS NULL )
     BEGIN    
        ROLLBACK TRAN
        -- eventually RAISERROR ...
     END

Cheers
0
 
LVL 3

Expert Comment

by:ibro
ID: 6439505
agree with angel, thats the best way you can do filtering constraint.
the other way is to implement your logic in stored procedures and put all kind of checkings in there. in this case you have to forget about direct sql updates/inserts in the table from your front end application. you have to always use stored procedures for all kind of updates. this will give you good performance as well and you can implement complex business logic in sp.
0
 
LVL 1

Author Comment

by:stino
ID: 6440764
Thanks alot guys.  ibro, I'm gonna post some points for you as well because you did answer the initial question.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

809 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