CODEWITHSUNDEEP
sql-server-2008constraintscheck-constraints
Timofey
Timofey

Reputation: 2518

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

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.

Upvotes: 0

Views: 771

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

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

Upvotes: 1

Alex
Alex

Reputation: 2011

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

Upvotes: 1

bernd_k
bernd_k

Reputation: 11966

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.

Upvotes: 0

Related Questions