467,879 Members | 1,293 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,879 developers. It's quick & easy.

unique constraint to a column from another table

Is it possible to create a unique constraint to a column from another
table? For example:

tb_current:
current_names
--------------
aaa
bbb

tb_new:
new_name
--------
ccc

Now I want to create a constraint on tb_new.new_name to be unique with
respect to tb_current.current_names. However, tb_new.new_name should
not be unique to itself. So I should not be able to insert 'aaa' to
tb_new.new_name. But I should be able to insert 'ccc' to
tb_new.new_name.

Here's the script to reproduce this example:

create table tb_current
(
current_names varchar(10)
)
create table tb_new
(
new_name varchar(10)
)
insert tb_current values ('aaa')
insert tb_current values ('bbb')
insert tb_new values ('ccc')

select * from tb_current
select * from tb_new

insert tb_new values ('aaa') -- this should NOT be allowed
insert tb_new values ('ccc') -- this should be allowed

Oct 29 '05 #1
  • viewed: 1913
Share:
3 Replies
It's hard to understand your spec. Is it that what you want is that any
new_name that you insert into tb_new does not exist in tb_current? If so, a
trigger will do it:

create trigger triu_tb_new on tb_new after insert, update
as
if @@ROWCOUNT = 0
return

if exists (select * from inserted i
join tb_current c on c.current_names = i.new_name)
begin
raiserror ('Names exist in tb_current.', 16, 1)
rollback tran
end
go

You can put a similar trigger on tb_current:

create trigger triu_tb_new on tb_new after insert, update
as
if @@ROWCOUNT = 0
return

if exists (select * from inserted i
join tb_new_name n on i.current_names = n.new_name)
begin
raiserror ('Names exist in tb_new_name.', 16, 1)
rollback tran
end
go

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<uw********@email.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
Is it possible to create a unique constraint to a column from another
table? For example:

tb_current:
current_names
--------------
aaa
bbb

tb_new:
new_name
--------
ccc

Now I want to create a constraint on tb_new.new_name to be unique with
respect to tb_current.current_names. However, tb_new.new_name should
not be unique to itself. So I should not be able to insert 'aaa' to
tb_new.new_name. But I should be able to insert 'ccc' to
tb_new.new_name.

Here's the script to reproduce this example:

create table tb_current
(
current_names varchar(10)
)
create table tb_new
(
new_name varchar(10)
)
insert tb_current values ('aaa')
insert tb_current values ('bbb')
insert tb_new values ('ccc')

select * from tb_current
select * from tb_new

insert tb_new values ('aaa') -- this should NOT be allowed
insert tb_new values ('ccc') -- this should be allowed

Oct 29 '05 #2
<uw********@email.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
Is it possible to create a unique constraint to a column from another
table? For example:

tb_current:
current_names
--------------
aaa
bbb

tb_new:
new_name
--------
ccc

Now I want to create a constraint on tb_new.new_name to be unique with
respect to tb_current.current_names. However, tb_new.new_name should
not be unique to itself. So I should not be able to insert 'aaa' to
tb_new.new_name. But I should be able to insert 'ccc' to
tb_new.new_name.

Here's the script to reproduce this example:

create table tb_current
(
current_names varchar(10)
)
create table tb_new
(
new_name varchar(10)
)
insert tb_current values ('aaa')
insert tb_current values ('bbb')
insert tb_new values ('ccc')

select * from tb_current
select * from tb_new

insert tb_new values ('aaa') -- this should NOT be allowed
insert tb_new values ('ccc') -- this should be allowed


I suspect it would be a mistake to implement it that way, although in
prinicple you could do so through triggers.

Why not just have one table for the common "Name" attribute and a unique key
for that column. Add an extra column to distinguish between "current" and
"new" names if that distinction is important.

--
David Portas
SQL Server MVP
--
Oct 29 '05 #3
Tom Moreau wrote:
It's hard to understand your spec. Is it that what you want is that any
new_name that you insert into tb_new does not exist in tb_current? If so, a
trigger will do it:


Yes, that is what I want. Thanks for the suggestion, I think that will
work.

Oct 29 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

26 posts views Thread by Agoston Bejo | last post: by
7 posts views Thread by Brian Keating | last post: by
3 posts views Thread by Bruce Wood | last post: by
10 posts views Thread by Laurence | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.