CODEWITHSUNDEEP
sqlsql-server
Chris
Chris

Reputation: 444

SQL Server - Check Constraint - Without a subquery?

I'm using SQL Server 2012, and I have a table which has a column IDSitesPtoP (int) and it contains a foreign key reference to another table. Call the first table, 'TableA'. The second table, TableB has a column IDSite (int) which is a primary key for TableB.

I would like to have a check constraint on TableA.IDSitePtoP which basically confirms that the value provided is present in TableB.IDSite, but filtered with a where clause. I believe I could do this with a subquery in the table definition of the column like this:

IDSitePtoP int CONSTRAINT CHECK (NOT EXISTS
    (SELECT IDSite FROM TableB WHERE TableB.IsDC=0));

Unfortunately this method doesn't seem to work, any idea what I have wrong there?

The error I get is:

Msg 1046, Level 15, State 1, Line 12
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Should I be approaching this completely differently, it seems to me that this functionality isn't supported in SQL Server...?

Upvotes: 2

Views: 436

Answers (2)

Vaibhav
Vaibhav

Reputation: 303

I don't think you need any check Constraint, because you cannot insert records in foreign key column if that record is not present in Primary Key.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Unfortunately, jut fixing the syntax error doesn't solve the problem:

IDSitePtoP int CONSTRAINT CHECK
    (TableA.IDSitePtoP IN (SELECT IDSite FROM TableB WHERE TableB.IsDC=0));

Subqueries are not allowed in constraints. You could create a UDF to do this. But, there is another way that might work, assuming that IDSITE, IsDC is unique in TableB. If so, create a unique index and use a foreign key index:

create unique index tableB(IdSite, IsDC);

create table . . .
    IDSItePtoP int,
    IsDc = 0,
    foreign key (IDSItePtoP, IsDC) references TableB(IdSite, IsDC)

Note that I'm using a computed column for the foreign key constraint. To be honest, I've never created a foreign key constraint with a constant value, so I'm not 100% sure that will work.

Upvotes: 3

Related Questions