stino
asked on
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?
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?
sorry...i missed something
CONSTRAINT myconstraint
FOREIGN KEY (MyValue)
REFERENCES ReferenceTable ( SomeValue)
CONSTRAINT myconstraint
FOREIGN KEY (MyValue)
REFERENCES ReferenceTable ( SomeValue)
ASKER
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
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
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thanks alot guys. ibro, I'm gonna post some points for you as well because you did answer the initial question.
FOREIGN KEY
REFERENCES ReferenceTable ( SomeValue)