CODEWITHSUNDEEP
sql-server-2008
user1690973
user1690973

Reputation: 19

How to set a column with varchar data type as unique in SQL Server 2008?

Table ApprovedLeave

Column names:

I want to set Leave column unique so that user cannot enter same date twice. I cannot take employee id as primary key because same employee can take leave multiple times

Upvotes: 0

Views: 3003

Answers (4)

Martin
Martin

Reputation: 363

Create a column for date. SQL Server 2008 has a new date datatype instead of using datetime. Don't use a varchar(max) to represent a date.

LeaveDate ( date )

Now your primary key must include the new column. 

alter table ApprovedLeave add constraint PK_ApprovedLeave primary key ( EmployeeID, LeaveDate ); 
/* don't forget to drop it if already exists */

This means that you can enter multiple records for the same employee, but not on the same date.

Upvotes: 1

Giscard Biamby
Giscard Biamby

Reputation: 4609

Building on doublesharp's answer, you will have to convert the Leave column data type to datetime. Depending on the format of the date values already in that column you will have to tweak the code a bit to handle the format(s): 

-- // Add new column using correct data type
alter table [tablename] add Leave_New datetime not null
go
-- // populate it from existing column:
update [tablename] set Leave_New = convert(datetime, [Leave], 1)
go

-- // Add the unique constraint:
ALTER TABLE [tablename]
ADD CONSTRAINT [unique_leave] UNIQUE NONCLUSTERED
(
    [EmployeeID], [Leave_new]
)


-- // After you verify that the above is successful, you can drop the old column, and rename the new one: 
alter table [tablename] drop column [Leave]
go
exec sp_RENAME '[tablename].Leave_New', 'Leave' , 'COLUMN'

Upvotes: 0

Ann L.
Ann L.

Reputation: 13965

Since you said that Leave represents a date range, you could represent your table as 

EmployeeID  int
LeaveStart  date
LeaveEnd    date
Approved    bit

... and make EmployeeID, LeaveStart, and LeaveEnd the components of a composite unique index.

But that only solves a subset of your real problem, which is that you don't want employees to take leave on overlapping time periods. (The exact same start and end dates are a special case of overlap). Consider these records:

Employee ID = 101, LeaveStart = 1/1/2012, LeaveEnd = 2/1/2012 
Employee ID = 101, LeaveStart = 1/1/2012, LeaveEnd = 2/2/2012

That would satisfy your unique constraint, but would still be very wrong.

You can address that with a trigger, or with a constraint involving a UDF, or through very careful business logic in your calling program, but an index won't be enough.

Upvotes: 0

doublesharp
doublesharp

Reputation: 27599

You need to use a UNIQUE CONSTRAINT on multiple columns - EmployeeID andLeave. Just setting the Leave column as unique will mean that it will be enforced regardless of the EmployeeID, and I am assuming you want to allow different employees to use the same Leave date.

ALTER TABLE [tablename]
ADD CONSTRAINT [unique_leave] UNIQUE NONCLUSTERED
(
    [EmployeeID], [Leave]
)

Upvotes: 0

Related Questions