Check constraint references another column in same table

Question

I have this table but check constraint is throwing me an error:

Column CHECK constraint for column 'MedicamentRegulated' references another column, table 'Medicaments'.

I've seen this as allowed in SQL Server but I don't know why it doesn't work :/

CREATE TABLE Medicaments (
MedicamentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50),
DateValidity DATE,
MedicamentRegulated BIT CHECK((MedicamentRegulated = 1 AND DateValidity IS NOT NULL) 
  OR MedicamentRegulated = 0)
)

Answer 1

The syntax you are using is for a column-level constraint, so it can only apply to the column it is attached to... try naming your constraints at the table level instead (this also allows encourages you to give your constraints meaningful names instead of the terrible system defaults):

CREATE TABLE dbo.Medicaments -- always use schema*
(
  MedicamentID INT IDENTITY(1,1) NOT NULL,
  Name VARCHAR(50),
  DateValidity DATE,
  MedicamentRegulated BIT,
  CONSTRAINT pk_Med PRIMARY KEY (MedicamentID),
  CONSTRAINT ck_Med_reg CHECK
  (
    (MedicamentRegulated = 1 AND DateValidity IS NOT NULL) 
    OR MedicamentRegulated = 0
  )
);

*see this post.