Unique Column - unique constraint won't do!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Unique Column - unique constraint won't do!

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-19 : 09:55:25

Hi,


USE tempdb

CREATE TABLE UniqueColumnTable
(
pk int IDENTITY,
fk int,
selected bit
)

INSERT UniqueColumnTable (fk, selected) VALUES (1, 0)
INSERT UniqueColumnTable (fk, selected) VALUES (1, 1)
INSERT UniqueColumnTable (fk, selected) VALUES (2, 0)
INSERT UniqueColumnTable (fk, selected) VALUES (2, 1)
INSERT UniqueColumnTable (fk, selected) VALUES (2, 0)

UPDATE UniqueColumnTable SET selected = 1 WHERE pk = 5

SELECT * FROM UniqueColumnTable

DROP TABLE UniqueColumnTable

I want the 'update' in the above code to fail. Basically, it must be impossible for any group of rows with the same 'fk' value for more than one row within the group to have 'selected' equal to 1.

A group of rows which share the same 'fk' value doesn't have to have a row with 'selected' equal to 1 - just that, if there is, it must be the only one.

How can I have the database enforce this, in particular, during concurrent updates?

Cheers,

XFactor

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-19 : 10:11:50

I have come up with the following for the update but I'm not convinced its water tight against concurrent transactions.

UPDATE
	UniqueColumnTable
SET
	selected = 1
FROM
	UniqueColumnTable u1
WHERE
	pk = 5
	and
	NOT EXISTS (
	SELECT TOP 1
		pk
	FROM
		UniqueColumnTable u2
	WHERE
		u2.selected = 1
		and u2.fk = u1.fk
	)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-06-19 : 14:20:46

http://support.microsoft.com/default.aspx?scid=kb;en-us;322002

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-19 : 14:59:47

That's awesome. Thanks.

Still, I'm not convinced by 'The Trigger Solution'.

1. Transaction 1 inserts row.
2. Transaction 1 checks there's no duplicates.
3. Transaction 1 still hasn't committed.
4. Transaction 2 inserts duplicate row.
5. Transaction 2 checks there's no duplicates.
6. Transaction 1 and 2 both commit.
======
There's duplicates in the table!

Is this possible? Is step 5 actually going to block on transaction 1's inserted row and so not be able to finish checking until transaction 1 has commited?

Cheers,

WT.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-19 : 15:14:41

Why not try it and see

In one query analyser window
create table a (i int)
go

create trigger tr on a for insert
as
select max(i) from a
go

begin tran
insert a select 1

Then in another
insert a select 2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-19 : 15:48:23

Well it does indeed block.

However, the following causes a dead lock at point 4...

1. Transaction 1 inserts row.
2. Transaction 1 still hasn't committed.
3. Transaction 2 inserts duplicate row.
4. Transaction 1 checks there's no duplicates.
-----------------------------------------------
5. Transaction 2 checks there's no duplicates.
6. Transaction 1 and 2 both commit.

I suspect this doesn't happen with 'The Calculated Column Solution'. What do you think?

Cheers,

XFactor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources