sql server - How do I modify this SQL schema to constrain data in tables? -

newbie in need of help.

i'm creating small database.

i want constrain data in couple of tables - lender , pingtree. providertype table contains lookup data , contains either 'lender' or 'pingtree'. how can modify structure lender table can contain lender types , pingtree, pingtree types?

guessing providertypeid column in provider table distinguishes between 2 types, must add same column both lender , pingtree tables, add suitable key (if doesn't exist) in provider on id, providertypeid, , add composite foreign key constraint lender , pingtree tables include these columns.

while may sound drag, known pattern called supertyping/subtyping. when supertype (provider) can multiple subtypes, don't need typeid column. when subtypes mutually exclusive, must do.

it might this:

alter table dbo.lender add providertypeid tinyint not null    constraint df_lender_providertypeid default (1)    constraint ck_lender_providertypeid_is_lender check (providertypeid = 1); alter table dbo.pingtree add providertypeid tinyint not null    constraint df_pingtree_providertypeid default (2)    constraint ck_pingtree_providertypeid_is_pingtree check (providertypeid = 2);  -- of pk, unique constraint, or unique index alter table dbo.provider add constraint uq_provider_id_providertypeid    unique (id, providertypeid);  alter table dbo.lender drop constraint fk_lender_providerid; alter table dbo.lender add constraint fk_lender_providerid_providertypeid    foreign key (id, providertypeid) references dbo.provider (id, providertypeid);  alter table dbo.pingtree drop constraint fk_pingtree_providerid; alter table dbo.pingtree add constraint fk_pingtree_providerid_providertypeid    foreign key (id, providertypeid) references dbo.provider (id, providertypeid); 

if written correctly (specifying column list on insert) stored procedures , application sql code should not have change.