|
Posted by David Portas on 10/15/77 11:30
<uw_badgers@email.com> wrote in message
news:1130545124.342622.45140@g47g2000cwa.googlegroups.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
--
Navigation:
[Reply to this message]
|