SQL Server 2008: Restrict column value with respect to another column value

Question

I have a table with 2 columns

CREATE TABLE mytable
(
  x int
  y char(1)
)

I would like to enforce constraint on y with respect to the value of x.

For example, when x = 5 I want to have y='a', when x = 12 y = 'b' and so on. Is it possible to do in SQL Server 2008? Something like

case when x = 5 then y='a' end

The latter statement does not work, therefore, I am asking for analog.

Answer 1

CREATE TABLE mytable
(
  x int not null,
  y char(1) not null,
  constraint CK_mytable_myrules CHECK (
    1 = CASE
          WHEN x=5 AND y='a' THEN 1
          WHEN x=12 AND y='b' THEN 1
        /* More rules here */
          ELSE 0 END
)

A CASE expression always has to return a value. Booleans are not part of SQL.

---

Or, if the general rule is "if we haven't matched any of these rules, let it through", then you have two choices - nested CASEs, or "fallback" rules (my own, just invented term):

nested CASEs:

    1 = CASE
          WHEN x=5 THEN
             CASE WHEN y='a' THEN 1
                  ELSE 0 END
          WHEN x=12 THEN
             CASE WHEN y='b' THEN 1
                  ELSE 0 END
        /* More rules here */
          ELSE 1 END

or the other way:

    1 = CASE
          WHEN x=5 AND y='a' THEN 1
          WHEN x=5 THEN 0
          WHEN x=12 AND y='b' THEN 1
          WHEN x=12 THEN 0
        /* More rules here */
          ELSE 1 END

Answer 2

You can set the expression to:

( x = 5 AND y = 'a' ) OR ( x = 12 AND y = 'b' )

Add more rules if you like...

Add the constraint to your table with this SQL:

ALTER TABLE dbo.myTable WITH NOCHECK  
      ADD  CONSTRAINT CK_myTable
      CHECK  (( x = 5 AND y = 'a' ) OR ( x = 12 AND y = 'b' ));

Check this reference for more examples:

http://www.databasejournal.com/features/mssql/article.php/3811831/Using-Check-Constraints-to-Validate-Data-in-SQL-Server.htm

Answer 3

It is possible to set up such constraints. Cf. link text You need to define a suitable UDF.

Ah there seem to be other possibilities too.