CODEWITHSUNDEEP
sqlsql-serverdatetimedate-arithmeticunique-index
Kamran
Kamran

Reputation: 1380

ADD CONSTRAINT on date part of a datetime column

I want to add a unique constraint on multiple columns. Usually, the following script should do the job:

CREATE UNIQUE INDEX uq_yourtablename
  ON dbo.yourtablename(column1, column2);

How about if columns2 is DateTime and we want to set the constraint only on the date part? One solution could be using triggers but I need to avoid that.

Upvotes: 0

Views: 498

Answers (1)

GMB
GMB

Reputation: 222502

You can use a computed column to virtually store the date part of the datetime column, and use it in the unique index.

create table yourtablename (
    column1 int, 
    column2 datetime, 
    column2_dt as convert(date, column2)
);

create unique index uq_yourtablename on yourtablename(column1, column2_dt);

Upvotes: 3

Related Questions