Primary key uniqueness - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Primary key uniqueness

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-17 : 10:46:32

I have two tables that use primary keys taken from the same 'source.
What is the constraint I must add so that there is no the same primary key value in both tables?
Thank you very much.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-17 : 11:41:29

I would write triggers on both tables to check if there is already that pk in use in that other table.

Hm...funny question.

No, you're never too old to Yak'n'Roll if you're too young to die.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-17 : 14:45:03

Create a union view on the two tables that includes the PK column, and then create a unique index on the view.

create view MyPKView
as
select MyPK from MyTable1
union all
select MyPK from MyTable2
go
create unique index IX_MyPKView__MyPK on MyPKView (MyPK)

CODO ERGO SUM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-17 : 15:46:32

You can't index a view than contains UNION.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-17 : 15:59:19

OK, then never mind.

CODO ERGO SUM

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-18 : 03:54:10

Can you give an example guys of such a trigger?

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-09-18 : 07:50:36

I guess we can create a global temp table and the trigger would insert in that table..
Before inserting in any of the 2 tables place a check if that record is present in the global temp table.. Don't really have much idea of global temp tables.. but u cud check out..

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-18 : 07:56:51

global temp table doesn't work here

just check in the trigger if key is already used in another table

create instead of insert trigger

IF EXISTS(SELECT 1 FROM t2 INNER JOIN inserted i on i.PKEY = t2.PKEY) BEGIN
raise error here key already exists or just do nothing
END
ELSE BEGIN
do your insert here
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources