2

I'm trying to create a check constraint on a table so that ParentID is never a descendant of current record

For instance, I have a table Categories, with the following fields ID, Name, ParentID

I have the following CTE

WITH Children AS (SELECT ID AS AncestorID, ID, ParentID AS NextAncestorID FROM Categories UNION ALL SELECT Categories.ID, Children.ID, Categories.ParentID FROM Categories JOIN Children ON Categories.ID = Children.NextAncestorID) SELECT ID FROM Children where AncestorID =99

The results here are correct, but when I try to add it as a constraint to the table like this:

ALTER TABLE dbo.Categories ADD CONSTRAINT CK_Categories CHECK (ParentID NOT IN(WITH Children AS (SELECT ID AS AncestorID, ID, ParentID AS NextAncestorID FROM Categories UNION ALL SELECT Categories.ID, Children.ID, Categories.ParentID FROM Categories JOIN Children ON Categories.ID = Children.NextAncestorID) SELECT ID FROM Children where AncestorID =ID))

I get the following error:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Adding a semicolon before the WITH, didn't help.

What would be the correct way to do this?

Thanks!

Yisroel M. Olewski
  • 1,560
  • 3
  • 25
  • 41
  • if you really need a complex validation on database layer, better to do it in a trigger. – saman tr Mar 31 '19 at 08:45
  • You need to create a function returns `BIT` (True/False) and pass the `ParentID` to this function. Then `ALTER TABLE dbo.Categories ADD CONSTRAINT CK_Categories CHECK ( dbo.MyFunction(ParentID) = 1);` – Ilyes Mar 31 '19 at 08:56
  • @samantarighpeima Hi. I have the validation also in client side, the constraint is an additional level of security, for existing records – Yisroel M. Olewski Mar 31 '19 at 09:29
  • @Sami Might be an idea, but since I have several such fields in several databases, that would require quite some overhead. no way to do it as a constraint? – Yisroel M. Olewski Mar 31 '19 at 09:30

1 Answers1

4

Per the SQL Server documentation on column constraints:

CHECK

Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

logical_expression

Is a logical expression used in a CHECK constraint and returns TRUE or FALSE. logical_expression used with CHECK constraints cannot reference another table but can reference other columns in the same table for the same row. The expression cannot reference an alias data type.

(The above was quoted from the SQL Server 2017 version of the documentation, but the general principle applies to all previous versions as well, and you didn't state what version you are working with.)

The important part here is the "cannot reference another table, but can reference other columns in the same table for the same row" (emphasis added). A CTE would count as another table.

As such, you can't have a complex query like a CTE used for a CHECK constraint. Like Saman suggested, if you want to check against existing data in other rows, and it must be in the DB layer, you could do it as a trigger.

However, triggers have their own drawbacks (e.g. issues with discoverability, behavior that is unexpected by those who are unaware of the trigger's presence).

As Sami suggested in their comment, another option is a UDF, but that's not w/o its own issues, potentially with both performance and stability according to the answers on this question about this approach in SQL Server 2008. It's probably still applicable to later versions as well.

If possible, I would say it's usually best to move that logic into the application layer. I see from your comment that you already have "client-side" validation. If there is an app server between that client and the database server (such as in a web app), I would suggest putting that additional validation there (in the app server) instead of in the database.

Community
  • 1
  • 1
Kanmuri
  • 1,105
  • 8
  • 11