Solved

Subquery in Check constraint

Posted on 2001-08-29
6
2,061 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 143

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

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.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

623 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