Subquery in Check constraint

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?
LVL 1
stinoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
ibroCommented:
CONSTRAINT myconstraint
  FOREIGN KEY  
            REFERENCES ReferenceTable ( SomeValue)  
           
0
 
ibroCommented:
sorry...i missed something

CONSTRAINT myconstraint
 FOREIGN KEY  (MyValue)
           REFERENCES ReferenceTable ( SomeValue)  
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
stinoAuthor Commented:
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
 
ibroCommented:
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
 
stinoAuthor Commented:
Thanks alot guys.  ibro, I'm gonna post some points for you as well because you did answer the initial question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.