|
Home > Archive > Microsoft SQL Server forum > March 2005 > CHECK Constraint to prevent a conditional duplicate
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
CHECK Constraint to prevent a conditional duplicate
|
|
| pdlevine@gmail.com 2005-03-31, 8:03 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:03 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
>
| |
| Hugo Kornelis 2005-03-31, 8:03 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)
| |
| --CELKO-- 2005-03-31, 8:03 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.
|
|
|
|
|