|
|
Posted by Erland Sommarskog on 06/10/05 00:53
[posted and mailed, please reply in news]
(kjaggi@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[Back to original message]
|