CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlunique-constraintcheck-constraints
Animesh D
Animesh D

Reputation: 5002

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

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.

Upvotes: 1

Views: 334

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

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

Upvotes: 6

Diego
Diego

Reputation: 36146

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

Upvotes: 2

Related Questions