Is it possible to associate Unique constraint with a Check constraint?

Question

I have a table access whose schema is as below:

create table access (
    access_id int primary key identity,
    access_name varchar(50) not null,
    access_time datetime2 not null default (getdate()),
    access_type varchar(20) check (access_type in ('OUTER_PARTY','INNER_PARTY')),
    access_message varchar(100) not null,
)

Access types allowed are only OUTER_PARTY and INNER_PARTY.

What I am trying to achieve is that the INNER_PARTY entry should be only once per day per login (user), but the OUTER_PARTY can be recorded any number of times. So I was wondering if its possible to do it directly or if there is an idiom to create this kind of restriction.

I have checked this question: Combining the UNIQUE and CHECK constraints, but was not able to apply it to my situation as it was aiming for a different thing.

Thanks for the tag. I stupidly put it in the question. Is it correct to create the clustered index on `access_type`? Also if I create a view, it is not possible to update the underlying table, is it? If I am not wrong, an instead of trigger has to be used to update the view, but that makes it complicated. — Animesh, Mar 22 '12 at 11:10
@Damien_The_Unbeliever - (cleaned up comments) Agreed. Don't need to use an indexed view for this as your answer shows. — Martin Smith, Mar 22 '12 at 11:37

Damien_The_Unbeliever · Accepted Answer · 2012-03-22T11:41:31.983

A filtered unique index can be added to the table. This index can be based on a computed column which removes the time component from the access_time column.

create table access (
    access_id int primary key identity,
    access_name varchar(50) not null,
    access_time datetime2 not null default (SYSDATETIME()),
    access_type varchar(20) check (access_type in ('OUTER_PARTY','INNER_PARTY')),
    access_message varchar(100) not null,
    access_date as CAST(access_time as date)
)
go
create unique index IX_access_singleinnerperday on access (access_date,access_name) where access_type='INNER_PARTY'
go

Seems to work:

--these inserts are fine
insert into access (access_name,access_type,access_message)
select 'abc','inner_party','hello' union all
select 'def','outer_party','world'
go
--as are these
insert into access (access_name,access_type,access_message)
select 'abc','outer_party','hello' union all
select 'def','outer_party','world'
go
--but this one fails
insert into access (access_name,access_type,access_message)
select 'abc','inner_party','hello' union all
select 'def','inner_party','world'
go

Damien: is there a reason why you used sysdatetime() instead of getdate()? doesn't sysdatetime need more storage? — Animesh, Mar 22 '12 at 12:03
@KishorNanda - Storage depends on datatype. `datetime2` in your case so the extra precision is "free" — Martin Smith, Mar 22 '12 at 12:06
Oh I see. Your answer worked for me very well and also had improved my understanding of indexes. Thank you. — Animesh, Mar 22 '12 at 12:16

score 2 · Answer 2 · answered Mar 22 '12 at 11:13

2

unfortunately you cant add a "if" on a check constraint. I advise using a trigger:

create trigger myTrigger
on access
instead of insert
as
begin
  declare @access_name varchar(50)
  declare @access_type varchar(20)
  declare @access_time datetime2

  select @access_name = access_name, @access_type= access_type, @access_time=access_time from inserted

  if exists (select 1 from access where access_name=@access_name and access_type=@access_type and access_time=@access_time)  begin
    --raise excetion
  end else  begin
    --insert
  end
end

you will have to format the @access_time to consider only the date part

answered Mar 22 '12 at 11:13

Diego

34,802
21
91
134

This can fail and allow duplicates for multirow inserts or under snapshot isolation. Better to get SQL Server to enforce the constraint as it's logic will be correct. Also you've forgotten about updates. – Martin Smith Mar 22 '12 at 11:15
I don't know why people are so against triggers. It can be changed to deal with multirow inserts if necessary, just loop trough the inserted table. And how exactly will you make SQL Server enforce the constraint in this case? That's the whole point of the question. – Diego Mar 22 '12 at 11:19
via a unique constraint on a view. triggers tend to be less efficient and less likely to be correct. – Martin Smith Mar 22 '12 at 11:21
well, I just don't like the idea of indexing views because it persist the data on the DB (duplication) and, as far as I'm concerned, it has to be maintained after every operation on the base table – Diego Mar 22 '12 at 11:49

Is it possible to associate Unique constraint with a Check constraint?

2 Answers2