Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2087
  • Last Modified:

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?
0
stino
Asked:
stino
  • 3
  • 2
1 Solution
 
ibroCommented:
CONSTRAINT myconstraint
  FOREIGN KEY  
            REFERENCES ReferenceTable ( SomeValue)  
           
0
 
ibroCommented:
sorry...i missed something

CONSTRAINT myconstraint
 FOREIGN KEY  (MyValue)
           REFERENCES ReferenceTable ( SomeValue)  
0
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Guy Hengel [angelIII / a3]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:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now