CODEWITHSUNDEEP
t-sqlconstraintssql-server-2014
Ayyub
Ayyub

Reputation: 63

How to add check constraints in SQL Server?

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
);

Upvotes: 0

Views: 2726

Answers (1)

Chris Pickford
Chris Pickford

Reputation: 9001

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));

Upvotes: 3

Related Questions