SQL Server Performance Forum – Threads Archive

unique constraint

I have a table which has an unique constraint on field 1 and field 2. What is the best way to implement the following: say we have a record which already contains the value in the field 1 an field 2 as 100 and 200 respectively and I don’t want to allow the fields contain 200 and 100 in any new record or update. It is like a unique within a unique. Is there a feature in sQL 2K5 allow me to do that? I try to avoid using trigger. wingman

There arent any built in features like that. Either you need to enforce through constraints or triggers or through stored procedures. Looks like constraint doesnt work and trigger is not an option. That leaves only the procs. Assuming all your INSERTs to the table are going through a proc, you can do a quick check within the proc for existence of any such records before you do the INSERT. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

There is no strait forward way but… you can use computed column for this… drop table test
create table test (id int, id1 int, id2 as id + id1)
select * from test CREATE UNIQUE
INDEX [u1] ON [dbo].[test] ([id], [id1])
CREATE UNIQUE
INDEX [uc] ON [dbo].[test] ([id2])
insert into test
select 10, 100
go
insert into test
select 100, 10
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>