CODEWITHSUNDEEP
sql-serverdatedatetime
doe
doe

Reputation: 148

add a constraint on a datetime column

i want to to add a constraint onto a ID column and a date time column, so that an id can only be entered once in a day

alter table Table1  
...
add constraint pk_id Primary Key (datetime,ID)

If an id has been inserted for the following datetime 2015-03-17 12:48:00, it would not get inserted again on the same datetime, but if the time changes to 2015-03-17 12:45:00 the id gets entered again.

Is there a way to add the constraint to just the date part of the datetime column?

Upvotes: 0

Views: 1497

Answers (2)

Juan
Juan

Reputation: 3705

I don't think you can but you have different alternatives:

  • Change your column to just have the date part populated
  • Create a computed column where you remove the time part and create the unique index used this column instead.

EDIT: as per @a-ツ comment there are other options:

  • Split the column in two, one to store the date and other to store the time part, so you can create de index over the date one

Upvotes: 3

Ajay2707
Ajay2707

Reputation: 5798

You have to give composite primary key or check constraint..

Check this example. For composite key, on design mode, just select both column and right click and select "primary-key".

CREATE TABLE [dbo].[Table_1](
    [id] [int] NOT NULL,
    [datecolumn] [datetime] NOT NULL,
    [name] [varchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [id] ASC,
    [datecolumn] ASC
)

) ON [PRIMARY]

GO

insert into Table_1 values (1, '2014-03-17 00:00:00.000', 'othercolumnvalue')

insert into Table_1 values  (1, '2014-03-17 12:00:00.000', 'othercolumnvalue')
insert into Table_1 values  (1, '2014-03-17 02:10:59.000', 'othercolumnvalue')

--this will give error as you already entered the same value.
insert into Table_1 values (1, '2014-03-17 00:00:00.000', 'othercolumnvalue')

how do I make a composite key with SQL Server Management Studio?

Upvotes: -1

Related Questions