add a constraint on a datetime column

Question

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?

if you're not concerned with the time portion can you not just change it to a date instead of a datetime? — Tanner, Mar 17 '15 at 10:56
no i need the datetime for other needs.its just in this instance — doe, Mar 17 '15 at 10:58

Juan · Accepted Answer · 2015-03-17T11:18:22.430

3

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

edited Mar 17 '15 at 11:18

answered Mar 17 '15 at 10:56

Juan

3,675
20
34

1

3rd: he/she could split the column in a date and a time column – A ツ Mar 17 '15 at 11:15
Thanks. i thought they would be a way to do it with the datetime value, but i will split the columns up – doe Mar 17 '15 at 11:47

score -1 · Answer 2 · edited May 23 '17 at 10:24

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?

He wants to ingore the date time part: "Is there a way to add the constraint to just the date part of the datetime column?" — Juan, Mar 17 '15 at 11:14
thanks dear. I give the answer as he need. means if time part is differ, insert it. — Ajay2707, Mar 17 '15 at 13:28

add a constraint on a datetime column

2 Answers2