CASE Statement in Check constraint?

• Ned

  Ten Centuries

  Points: 1214

  More actions

  March 29, 2004 at 1:03 pm

  #179158

  Is there a way to put a CASE Statement in a table check constraint?

  For example, on the table:

  CREATE TABLE dbo.roysched

  (

      title_id [tid] NOT NULL,

      lorange  int   NULL,

      hirange  int   NULL,

      royalty  int   NULL,

  CONSTRAINT FK__roysched__title___0DAF0CB0

      FOREIGN KEY (title_id)

      REFERENCES dbo.titles (title_id)

  )

  I would like to do something like this:

  ALTER TABLE roysched

  ADD CONSTRAINT CK_title_id

  CHECK (CASE royalty WHEN 10 THEN title_id = 'BU2075')

  The intended effect being that the only title_id that can have a royalty of 10 is 'BU2075'.

  Thanks

  Noel

• Simon Sabin

  SSCrazy Eights

  Points: 8142

  More actions

  March 29, 2004 at 2:05 pm

  #500941

  Try this

  ALTER TABLE roysched

  ADD CONSTRAINT CK_title_id

  CHECK (CASE royalty WHEN 10 THEN 'BU2075' ELSE title_id  END = title_id  )

  
  Simon Sabin
  SQL Server MVP
  
  http://sqlblogcasts.com/blogs/simons

• Ned

  Ten Centuries

  Points: 1214

  More actions

  March 29, 2004 at 4:35 pm

  #500975

  That's the ticket! Thank you!