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?

Firebird's the only that I've seen that provides this obviously necessary functionality. — , Dec 26 '13 at 23:05
Check [here](https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=36494dae0355a12ebd4a823a52dd9883) and [here](https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=ef4434a485a8bebc44e27d5a0233047b). Quite bad bugs to ironed out! — Vérace, Sep 22 '19 at 06:00

Johanna Larsson · Accepted Answer · 2012-10-21T18:47:42.893

66

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')

edited Oct 21 '12 at 18:47

answered Oct 21 '12 at 18:17

Johanna Larsson

10,531
6
39
50

3

@Highland something like that? Note that you might have to add the schema prefix to myFunction when you use it. – Johanna Larsson Oct 21 '12 at 18:43
2

This approach worked for me, with the schema prefix before the function name (as noted in your comment). – Troy Gizzi Aug 11 '15 at 17:00
1

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." – Brian Leeming Jun 14 '16 at 14:33
I have List & ListEntries tables used to prevent tons of small lookup tables for constrained status choices. This will help a lot. – Hecatonchires Aug 08 '17 at 02:33
1

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). – gavenkoa Jan 22 '18 at 13:18
1

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. – WhyGeeEx Mar 28 '18 at 18:33
1

I might also add that it would be faster to return and evaluate a BIT datatype over a string-based 'TRUE'. – Bonez024 Apr 09 '19 at 14:38
1

This solution is unreliable: https://dba.stackexchange.com/questions/12779/how-are-my-sql-server-constraints-being-bypassed – DharmaTurtle Feb 13 '20 at 17:47

Gayanee Wijayasekara · Answer 2 · 2015-12-01T09:34:04.933

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')

UDFs in check constraints are unreliable: https://dba.stackexchange.com/questions/12779/how-are-my-sql-server-constraints-being-bypassed — DharmaTurtle, Feb 13 '20 at 17:48

score 3 · Answer 3 · answered Oct 21 '12 at 18:25

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.

answered Oct 21 '12 at 18:25

shahkalpesh

33,172
3
63
88

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'** – Highland Oct 21 '12 at 18:30
3

@Highland: well then define a primary key in `CodeTable` – Oct 21 '12 at 19:30
2

@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...* – marc_s Oct 21 '12 at 19:46
1

@marc_s: Yes I do have defined a primary key in the table. – Highland Oct 21 '12 at 19:53
1

@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... – marc_s Oct 21 '12 at 20:04
@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.) – philipxy Dec 27 '14 at 03:48

score 1 · Answer 4 · answered Feb 07 '17 at 19:52

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

Sub queries in check constraint

4 Answers4

Linked