CODEWITHSUNDEEP
sqlsql-servert-sqlssis
kk236
kk236

Reputation: 21

Primary Key Foreign key relationship with different values

I want to create a foreign key relationship in a table but the column which i want to give relationship has an extra character. For example in primary key if it is PPL in the other table(Foreignkey) it is PPL.M. Could some one tell me how can I give relationship between these 2 columns. How to avoid the extra character.

Regards KK

Upvotes: 2

Views: 235

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

May be you need to create Foreign Key with NOCHECK option

ALTER TABLE dbo.child_table
    WITH NOCHECK
    ADD CONSTRAINT FK_childtable FOREIGN KEY (child_col) REFERENCES 
    dbo.Parent_table(Parent_col)

Upvotes: 0

T I
T I

Reputation: 9933

You can add a computed column to the referenced table to trim the additional chatacter

alter table tablea
add coly as (substring(colx,1,5)) persisted

Then reference this column instead

alter table tableb
create constraint fk_tableb_colx 
foreign key (colx)
references tablea(coly)

Alternatively you could use a trigger

create trigger trg_tableb_fk_colx
on tableb
for insert, update
as
begin
    if exists(select 1 from inserted
              where inserted.colx not in(select substring(colx, 1, 5) from tablea))
    begin
        raiserror('violation of foreign key trg_tableb_fk_colx',16,1)
    end
end

Upvotes: 3

Related Questions