I'm trying to write a check constraint that uses a sub-query. Admittedly it's not the ideal solution, but to date it's the solution I've got to work with. Consider the following example table that is sufficiently similar to the real thing for sake of illustration:

CREATE TABLE ExampleTable (
    ExampleId INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    KeyName NVARCHAR(50) NOT NULL,
    KeyValue INTEGER NOT NULL,

    -- Various other fields go here

    CONSTRAINT "PK_ExampleTable" PRIMARY KEY CLUSTERED ("ExampleId" ASC)
)

I have to write a constraint that ensures that the supplied KeyName and KeyValue data, along with various particular combinations of the other fields, do not already occur within the same table. The sort of constraint I've developed for this is like the following:

ALTER TABLE ExampleTable
ADD CONSTRAINT ExampleTableConstraint CHECK 
( 
    (SELECT COUNT(*) FROM ExampleTable AS SubQueryTable
        WHERE SubQueryTable.[KeyName] = [KeyName] AND
              SubQueryTable.[KeyValue] = [KeyValue] AND
              -- Other parameters go here
     ) = 0
)

The constraint can be created just fine, and it doesn't give any errors with the first row I insert into the table, but after that it fails every time no matter what completely unique values I supply for all the fields. Some time debugging has convinced me that this is because it's not disambiguating the column references and is simply comparing the columns to themselves in the sub-query. Or to put it differently, it seems to be working as if I had written the constraint as follows:

ALTER TABLE ExampleTable
ADD CONSTRAINT ExampleTableConstraint CHECK 
( 
    (SELECT COUNT(*) FROM ExampleTable AS SubQueryTable
        WHERE SubQueryTable.[KeyName] = SubQueryTable.[KeyName] AND
              SubQueryTable.[KeyValue] = SubQueryTable.[KeyValue] AND
              -- Other parameters go here
     ) = 0
)

This naturally would fail every row but the first, which is exactly what I'm seeing. Can anybody tell me how I might disambiguate my columns in a check constraint sub-query? Thanks in advance!

asked 06 Feb '13, 15:01

JWilliston's gravatar image

JWilliston
1064410
accept rate: 0%

that the supplied KeyName and KeyValue data, along with various particular combinations of the other fields, do not already occur within the same table

So this is something not possible to specify with the help of a UNIQUE KEY (or possibly a UNIQUE index in case some further relevant fields may allow NULL values)?

(07 Feb '13, 03:23) Volker Barth

I'd try:

ALTER TABLE ExampleTable
ADD CONSTRAINT ExampleTableConstraint CHECK 
( 
    (SELECT COUNT(*) FROM ExampleTable AS SubQueryTable
        WHERE SubQueryTable.[KeyName] = ExampleTable.[KeyName] AND
              SubQueryTable.[KeyValue] = ExampleTable.[KeyValue] AND
              -- Other parameters go here
     ) = 0
)
permanent link

answered 07 Feb '13, 01:18

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

That seem to do the trick.

What I wasn't aware of (and therefore had a false impression one had to test for "SELECT COUNT(*) ... <= 1"), is the obvious fact that those constraints seem to get tested before the data modification is applied to the table.

As a consequence, a SELECT on the table the check constraint is defined on seems to return the table contents without the inserted values and both without the old and new contents of updated rows, whereas relating to the columns itself (possibly qualified with the table name) means the new contents.

(In case my new immpression is correct, that may be a detail worthwhile to add to the docs ...)

(07 Feb '13, 04:12) Volker Barth
1

I'm a little surprised as I expected within that scope that qualifying the columns with "ExampleTable" would basically be the same as using the alias. I was expecting some funky syntax as one must use to refer to the actual rows being diddled with in triggers. I guess I was over-thinking the problem. Many thanks!

(07 Feb '13, 11:16) JWilliston
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15
×10
×2

question asked: 06 Feb '13, 15:01

question was seen: 1,354 times

last updated: 07 Feb '13, 11:16