what type of Constraint to prevent duplicates

[kjaggi]

I am trying to either write a trigger or a check constraint to prevent
duplicates in my table. There are two columns I need to look at for the
duplicates and only one combo value for both columns is allowed in the
table. For e.g.
Column Serial can have only one '123456' value with testresult value as
'PASS'. This serial can be in the table many times with any other combo
so for e.g.
The table could contain 100 entries for serial column value '123456'
with testresult value 'FAIL', 'PENDING' etc.

** TESTED SCHEMA BELOW **

-- create table
CREATE TABLE bstresult
(ID int IDENTITY (1, 1) NOT NULL ,
serial char (10) NULL ,
testresult char (10) NULL
)
-- Insert valid values
insert into bstresult values ('123456','PASS')
insert into bstresult values ('123456','FAIL')
insert into bstresult values ('123456','FAIL')
insert into bstresult values ('123456','PENDING')

-- insert invalid value this should fail

insert into bstresult values ('123456','PASS')

If I simply create a unique constraint on both columns it will not
allow the FAIL combo or PENDING combo with the same serial which I need
to allow.

Appreciate your help.

[Chandra]

Hi
For this you can create a composite primary key or this can be done by
using INSTEAD OF INSERT Trigger
something like this:

CREATE TRIGGER <TriggerName>
INSTEAD OF INSERT
AS
BEGIN

IF NOT EXISTS(SELECT * FROM bstresult INNER JOIN INSERTED ON
INSERTED.serial = bstresult.serial AND INSERTED.testresult =
bstresult.testresult)
INSERT INTO bstresult SELECT * from INSERTED

END

please let me know if it worked.

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

[posted and mailed, please reply in news]

(kj****@hotmail.com) writes:

If I simply create a unique constraint on both columns it will not
allow the FAIL combo or PENDING combo with the same serial which I need
to allow.

Here are a couple of variations. You can use an indexed view:

CREATE VIEW showstopper WITH SCHEMABINDING AS
SELECT serial FROM dbo.bstresult WHERE testresult = 'PASS'
go
CREATE UNIQUE CLUSTERED INDEX xyz ON showstopper(serial)

You can use an indexed computed column:

CREATE TABLE bstresult
(ID int IDENTITY (1, 1) NOT NULL ,
serial char (10) NULL ,
testresult char (10) NULL,
pass AS CASE testresult
WHEN 'PASS' THEN serial
ELSE convert(varchar, ID)
END CONSTRAINT u_pass UNIQUE(pass)
)

Both this methods requires that you have the settings ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL, ARITHABORT, ANSI_NULLS, QUOTED_IDENTIFIER
and ANSI_PADDING on. Of these, the settings of ANSI_NULLS and
QUOTED_IDENFIFIER are stored with stored procecures, and the setting
of ANSI_PADDING is stored with the table column. These settings are
on by default when you connect with local API, except for ARITHABORT,
which you need to set on.

Yet a method is to use a trigger:

CREATE TRIGGER unique_pass ON bstresult FOR INSERT, UPDATE AS
IF EXISTS (SELECT b.serial
FROM inserted i
JOIN bstresult b ON i.serial = b.serial
AND i.testresult = b.testresult
AND i.ID <> b.ID
WHERE i.testresult = 'PASS')

BEGIN
ROLLBACK TRANSACTION
RAISERROR('Attempt to insert duplicate PASS record', 16, 1)
END

The INSTEAD OF trigger Chandra posted permits you ignore the duplicate
rather than raising an error. Unfortunately, this approach is somewhat
problematic, since it does not handle multi-rows insert well. If you
insert many rows, and one is a duplicate, all rows are dropped on the
floor. On the other hand, if you insert two duplicate rows, but that
serial are not in the table, both rows make into the table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On 8 Jun 2005 18:39:26 -0700, kj****@hotmail.com wrote:

(snip)

If I simply create a unique constraint on both columns it will not
allow the FAIL combo or PENDING combo with the same serial which I need
to allow.

Hi kjaggi,

You could use an indexed view for this. I'm typing this example from the
top of my head, so I might have the syntax wrong!

CREATE VIEW FailedResults WITH SCHEMABINDING
AS SELECT serial
FROM dbo.bstresult
WHERE testresult = 'PASS'
go
CREATE UNIQUE CLUSTERED INDEX ix_FailedResults
ON dbo.FailedResults(serial)
go
Best, Hugo
--

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