Unique across tables - 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 2000 Forums

SQL Server Development (2000)

Unique across tables

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-04-13 : 09:56:26

Hi,

Lets say I have..

Address
=======
AddressID int pk
PostCode varchar 50
...

And then...

User
====
AddressID int fk
...

Company
=======
AddressID int fk
...

Is there any way to stop a User and a Company referencing the same Address? In otherwords, the referencing AddressID needs to be unique across different tables.

Cheers,

XFactor.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-04-13 : 11:18:04

In essence, you want to effect a CONSTRAINT that involves multiple tables. To me, that means implementing a trigger.

[CODE]
CREATE TRIGGER iuUser
for Insert, Update
as
begin
-- If the AddressId inserted/updated exists in the other table, rollback and raise an error
if exists (select * from Company c join Inserted i on i.AddressID = c.AddressID)
begin
RaisError(...)
rollback
end
end
[/CODE]

You'll need to apply a trigger to each table that references the other table.

HTH

=================================================================
Every act of conscious learning requires the willingness to suffer an injury to one's self-esteem. That is why young children, before they are aware of their own self-importance, learn so easily; and why older persons, especially if vain or important, cannot learn at all. -Thomas Szasz, author, professor of psychiatry (1920- )

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-04-13 : 12:53:39

Thanks for your reply. That's what I thought one would have to do. I think I'll just leave it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources