Sub queries in check constraint

Question

I have table designed in SQL-Server 2008 R2.

I have a column in that table which needs to be checked against another table when data is inserting.

ALTER TABLE Table1
        WITH CHECK ADD CONSTRAINT CK_Code
        CHECK (MyField in (Select Field From Table2))

This cause an error

Sub-queries are not allowed in this context. Only scalar expressions are allowed.

I have looked at this question about Check Constraint - Subqueries are not allowed in this context.

Is there any way of achieving this without using a trigger?

Rossner

Wouldn't the foreign key constraint be helpful? – Wiesbaden

Firebird's the only that I've seen that provides this obviously necessary functionality. – Culminate

Check here and here. Quite bad bugs to ironed out! – Loran

Answer 1

Note, what you really want is a foreign key constraint. That said, to get a "query" into a check you can write a function that contains the query and outputs a scalar value, then use that function in the check constraint.

CREATE FUNCTION myFunction (
    @field DATATYPE(?)
)
RETURNS VARCHAR(5)
AS
BEGIN
    IF EXISTS (SELECT* FROM Table2 WHERE MYFIELD = @field)
        return 'True'
    return 'False'
END

Something like that. Not tested.

Then you can add it to your check like so

ALTER TABLE Table1
    WITH CHECK ADD CONSTRAINT CK_Code
    CHECK (myFunction(MYFIELD) = 'True')

Translucent

@Rossner something like that? Note that you might have to add the schema prefix to myFunction when you use it. – Translucent

This approach worked for me, with the schema prefix before the function name (as noted in your comment). – Cobbs

In my case I have 2 tables that are joined with PK/FK relationship and each one has a foreign key to a third table. I wanted to make sure both foreign keys match. The FK is in both tables to simplify the queries used. This technique worked well after adding "dbo." – Lambart

I have List & ListEntries tables used to prevent tons of small lookup tables for constrained status choices. This will help a lot. – Topical

Calling function with side effects in check is not a proper way to solve task. To make check atomic function should acquire lock on checking rows (or table). – Tol

What you want is a foreign key constraint, but you can't have one if there is no unique key on the table being referenced. That is my situation, and this solution addresses it admirably, thank you. – Amaya

I might also add that it would be faster to return and evaluate a BIT datatype over a string-based 'TRUE'. – Sales

This solution is unreliable: dba.stackexchange.com/questions/12779/… – Whitver

Answer 2

You can't have sub queries inside check constraints. What you can do is use a UDF that returns a scalar value inside the check constraint.

Step 1: Create the table

USE CTBX
GO

CREATE TABLE RawMaterialByGender 
(
RMGID int primary key identity(1,1),
RMID smallint foreign key references RawMaterialMaster(RMID),
LeveLMasterID smallint foreign key references LevelMaster(LevelTextID),
IsDeleted bit
)

Step 2 : Create the UDF that returns a scalar

Create FUNCTION [dbo].[IsValidLevelMasterGender](@LevelMasterID smallint)

    RETURNS bit
    AS
    BEGIN
     DECLARE @count smallint;
     DECLARE @return bit;

     SELECT @count = count(LevelTextID)      
     FROM [LevelMaster]
     WHERE LevelCategoryID = 3 AND IsActive = 1 AND LevelTextID=@LevelMasterID

     IF(@count = 0)
     SET @return = 'false';
     ELSE
     SET @return = 'true';

     RETURN @return;

    END;
    GO

Step 3 : Alter table to add the CHECK constraint

ALTER TABLE RawMaterialByGender 
ADD CONSTRAINT check_LevelMasterID CHECK (dbo.IsValidLevelMasterGender(LeveLMasterID) = 'true')

Eatmon

UDFs in check constraints are unreliable: dba.stackexchange.com/questions/12779/… – Whitver

Answer 3

ALTER TABLE Table1
ADD CONSTRAINT FK_Table1_Code FOREIGN KEY (MyField)
REFERENCES Table2 (Field) ;

Ref: http://msdn.microsoft.com/en-us/library/ms190273.aspx
Note: I haven't checked the above for syntax.

Wiesbaden

Yes, I tried using that way which gives me an error There are no primary or candidate keys in the referenced table 'CodeTable' that match the referencing column list in the foreign key 'fk_Code' – Rossner

@Highland: well then define a primary key in CodeTable – Phycomycete

@Highland: then the question really is: why on earth doesn't your CodeTable have a primary key!?!?!? If it doesn't have a primary key, it's not really a table... – Tare

@marc_s: Yes I do have defined a primary key in the table. – Rossner

@Highland: but the error message you posted (in your first comment to this answer) seems to contradict this.... the primary key (or a unique key) needs to be on the column you're trying to create a reference to... – Tare

@Highland: SQL PK and UNIQUE NOT NULL actually mean superkey. And FK actually means foreign superkey. The error message should say "there is no matching superkey column list" ie one declared PK or UNIQUE NOT NULL. SQL requires that a FK referenced column list be declared PK or UNIQUE NOT NULL even though it would be enough that the referenced columns contain a superkey. (A CK is a superkey containing no smaller superkey.) – Consciousness

Answer 4

First of all in your example you clearly need FK Constraint.

Another posibility is to use view with WITH CHECK OPTION and give access to user through it:

CREATE TABLE Table1(i INT PRIMARY KEY, CK_Code CHAR(1));
CREATE TABLE Table2(Field CHAR(1));
INSERT INTO Table2(Field) VALUES ('A'),('B'), ('C');
GO

CREATE VIEW v_Table1
AS
SELECT *
FROM Table1
WHERE CK_code IN (SELECT Field FROM Table2)  -- here goes your subquery check
WITH CHECK OPTION;

When you try to insert data that violates your "constraint" like:

INSERT INTO v_Table1(i, CK_Code)
VALUES(10, 'D');

You will get:

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

LiveDemo

Shake