CODEWITHSUNDEEP
sqlsql-server-2008
Adrien
Adrien

Reputation: 21

SQL Server 2008 : Subqueries are not allowed in this context. Only scalar expressions are allowed

I want to create a database using SQL Server 2008. I have my sql query to create it. But everytime I want to execute it, I get this error message : "Subqueries are not allowed in this context. Only scalar expressions are allowed."

This is the part of the query where the problem is : 

alter table PRODUIT add constraint ID_PRODUIT_CHK
     check(exists(select * from PRODUCTION
                  where PRODUCTION.IdProduit = IdProduit));

There is something wrong with the "check" but I don't know what. Can you help me ?

Upvotes: 2

Views: 1777

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

A check constraint is only allowed to make assertions about the values of columns in the row. It's not allowed to reference other rows within the same table, let alone other tables.

There are some possible workarounds - as I say in my comment, it looks like you're trying to create a foreign key constraint. There are some fudges that may also work (by placing logic in a user defined function), but these usually have edge cases where the check will work during the initial insert/update, but can be violated later.

We'd need to understand your problem more clearly to suggest a workable solution. What are you trying to achieve. If it's not a foreign key constraint, what features of foreign key constraints don't work in your situation?

Upvotes: 4

Related Questions