By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,403 Members | 891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,403 IT Pros & Developers. It's quick & easy.

CHECK Constraint to prevent a conditional duplicate

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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/
<pd******@gmail.com> wrote in message news:11*********************@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

Jul 23 '05 #2

P: n/a
On 31 Mar 2005 08:37:47 -0800, pd******@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)
Jul 23 '05 #3

P: n/a
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.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.