CHECK Constraint to prevent a conditional duplicate

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
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.googlegro ups.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
>
|||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
>
|||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
>
|||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(SpecificID)
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)
|||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)
|||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.

CHECK Constraint to prevent a conditional duplicate

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?
-PaulCHECK 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
>|||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
>|||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
>|||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(SpecificID)
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)|||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)|||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.

CHECK Constraint to prevent a conditional duplicate

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?

-PaulCHECK 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.googlegro ups.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|||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(SpecificID)
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)|||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.

CHECK Constraint to prevent a conditional duplicate

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?
-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger inste
ad.
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
>|||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
>|||You can also create a view for STATUS = 2 and create a unique clustered inde
x
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
>|||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(SpecificID)
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)|||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 ind
ex
>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)|||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.