CODEWITHSUNDEEP
sql-servert-sqlconstraintsforeign-key-relationship
Venky
Venky

Reputation: 11

Not allowing column values other than what is found in other table

I have 2 tables 

Table A
Column A1 Column A2 and

Table B
Column B1 Column B2

Column A1 is not unique and not the PK, but I want to put a constraint on column B1 that it cannot have values other than what is found in Column A1, can it be done?

Upvotes: 1

Views: 94

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Here's the design I'd go with, if I'm free to create tables and triggers in the database, and still want TableA to allow multiple A1 values. I'd introduce a new table:

create table TableA (ID int not null,A1 int not null)
go
create table UniqueAs (
    A1 int not null primary key,
    Cnt int not null
)
go
create trigger T_TableA_MaintainAs
on TableA
after insert, update, delete
as
    set nocount on
    ;With UniqueCounts as (
        select A1,COUNT(*) as Cnt from inserted group by A1
        union all
        select A1,COUNT(*) * -1 from deleted group by A1
    ), CombinedCounts as (
        select A1,SUM(Cnt) as Cnt from UniqueCounts group by A1
    )
    merge into UniqueAs a
    using CombinedCounts cc
        on
            a.A1 = cc.A1
    when matched and a.Cnt = -cc.Cnt then delete
    when matched then update set Cnt = a.Cnt + cc.Cnt
    when not matched then insert (A1,Cnt) values (cc.A1,cc.Cnt);

And test it out:

insert into TableA (ID,A1) values (1,1),(2,1),(3,2)
go
update TableA set A1 = 2 where ID = 1
go
delete from TableA where ID = 2
go
select * from UniqueAs

Result:

A1          Cnt
----------- -----------
2           2

Now we can use a genuine foreign key from TableB to UniqueAs. This should all be relatively efficient - the usual FK mechanisms are available between TableB and UniqueAs, and the maintenance of this table is always by PK reference - and we don't have to needlessly rescan all of TableA - we just use the trigger pseudo-tables.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

Long way around but you could add an identity to A and declare the PK as iden, A1.
In B iden would just be an integer (not identity).
You asked for any other ways.

Could create a 3rd table that is a FK used by both but that does not assure B1 is in A.

Upvotes: 0

Sonam
Sonam

Reputation: 3466

You can not have dynamic constraint to limit the values in Table B. Instead you can either have trigger on TableB or you need to limit all inserts or updates on TbaleB to select values from Column A only:

 Insert into TableB
 Select Col from Table where Col in(Select ColumnA from TableA)

or

 Update TableB
 Set ColumnB= <somevalue>
 where <somevalue> in(Select columnA from TableA)

Also, I would add its a very design practice and can not guarantee accuracy all the time.

Upvotes: 0

Vasanth
Vasanth

Reputation: 1710

It cannot be done using FK. Instead you can use a check constraint to see if B value is available in A.

Example:

alter table TableB add constraint CK_BValueCheck check dbo.fn_ValidateBValue(B1) = 1

create function dbo.fn_ValidateBValue(B1 int)
returns bit as 
begin
      declare @ValueExists bit
      select @ValueExists  = 0

      if exists (select 1 from TableA where A1 = B1)
            select @ValueExists  = 1

      return @ValueExists
end

Upvotes: 1

Related Questions