How to add check constraints in SQL Server?

Question

I want to add the following constraint to the table named service in my database.

Constraint 1: Neither DateCompleted nor DueDate can precede StartDate.

CREATE TABLE [Service] (
  Service_ID     INT         NOT NULL PRIMARY KEY
, Invoice_ID     INT
, Project_ID     INT         NOT NULL
, Description    CHAR(20)    NOT NULL
, Start_Date     VARCHAR(10) NOT NULL
, Due_Date       VARCHAR(10)
, Planned_Price  VARCHAR(10)
, Actual_Price   VARCHAR(10)
, Status         CHAR(10)    NOT NULL
, Date_Completed VARCHAR(10)
);

Another constraint should be added to the table named client.

Constraint 2: Column Post_Code values should be positive integers with three or four digits.

CREATE TABLE [Client] (
  Client_ID      INT         NOT NULL PRIMARY KEY
, First_Name     VARCHAR(15) NOT NULL
, Last_Name      VARCHAR(15) NOT NULL
, Street_Address VARCHAR(50)
, Suburb         VARCHAR(15) NOT NULL
, State          VARCHAR(3)  NOT NULL
, Post_Code      INT         NOT NULL
, Phone_number   INT         NOT NULL
);

Chris Pickford · Accepted Answer · 2016-06-23 16:20:53Z

3

Service:

ALTER TABLE [Service]
ADD CONSTRAINT [service_datecheck]
CHECK ([StartDate] <= [DateCompleted] AND [StartDate] <= [DueDate]);

Client:

ALTER TABLE [Client]
ADD CONSTRAINT [client_postcodecheck]
CHECK ([Post_Code] > 0 AND LEN([Post_Code]) IN (3,4));

answered Jun 23, 2016 at 16:20

Chris Pickford

9,0606 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

paparazzo Over a year ago

Might fail on negative. >=100 <=9999 might be better

Chris Pickford Over a year ago

@Paparazzi I did think about doing it that way, and it's probably better, but I opted to match exactly what was in the question.

Ayyub Over a year ago

works perfectly for my my requirements. Hope you could resolve this too. please check stackoverflow.com/questions/38011099/…

Collectives™ on Stack Overflow

How to add check constraints in SQL Server?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related