MS SQL Server - CHECK Constraint to prevent a conditional duplicate

Author

CHECK Constraint to prevent a conditional duplicate

pdlevine@gmail.com

2005-03-31, 8:01 pm

Hi,

I need to enforce that a table does not have "duplicates" for a
specific status type in the table.

If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.

I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.

Just when the status = 2, there can not be any other rows with the same
ID and status = 2.

Any ideas?

-Paul

Tibor Karaszi

2005-03-31, 8:01 pm

CHECK constraint work at row-by-row basis. I suggest you use a trigger instead.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
http://www.sqlug.se/

<pdlevine@gmail.com> wrote in message news:1112287067.125360.22800@l41g2000cwc.googlegroups.com...
> Hi,
>
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
>
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
>
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
>
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
>
> Any ideas?
>
> -Paul
>

Jack

2005-03-31, 8:01 pm

Use trigger to enforce this requirement.

"pdlevine@gmail.com" wrote:

> Hi,
>
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
>
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
>
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
>
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
>
> Any ideas?
>
> -Paul
>
>

Alejandro Mesa

2005-03-31, 8:01 pm

You can also create a view for STATUS = 2 and create a unique clustered index
by [id] on the view.

Example:

use northwind
go

create table t (
colA int,
colB int
)
go

create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go

create unique clustered index ix_u_c_view1_colA on view1(colA)
go

insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go

insert into t values(1, 2)
go

select * from t
go

drop view view1
go

drop table t
go

AMB

"pdlevine@gmail.com" wrote:

> Hi,
>
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
>
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
>
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
>
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
>
> Any ideas?
>
> -Paul
>
>

Hugo Kornelis

2005-03-31, 8:01 pm

On 31 Mar 2005 08:37:47 -0800, pdlevine@gmail.com wrote:

>Hi,
>
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>
>Any ideas?
>
>-Paul

Hi Paul,

Apart from the trigger Tibor suggests, there are two other options:

1. Use an indexed view:

CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(Specific
ID)
go

2. Use a computed column (assuming PKCol is the primary key):

ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go

(both versions untested - bewarer of typos!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis

2005-03-31, 8:01 pm

On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:

>You can also create a view for STATUS = 2 and create a unique clustered index
>by [id] on the view.

Hi Alejandro,

Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

--CELKO--

2005-03-31, 8:01 pm

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Sponsored Links