Solved

Subquery in Check constraint

Posted on 2001-08-29
6
1,999 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

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 …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now