Vryjdfkk

create table CustomerTypes(

CustomerTypeID int primary key identity(1,1),

CustomerType varchar(11)

);





insert into CustomerTypes(CustomerTypeID,CustomerType

select 1, 'Regular' union all

select 2, 'Business'





create table Customers(

ContactID int primary key identity(1,1),

CustomerTypeID int references CustomerTypes(CustomerTypeID),

FirstName varchar(25)not null,

LastName varchar(25)not null,

DOB date not null,

Telephone varchar(18),

Notes varchar(250),

AddDate date not null

constraint cust_AltPK unique (ContactID,CustomerTypeID)

); 





create table Regular_Customer(

ContactID int primary key identity(1,1),

CustomerTypeID as 1 persisted, --regular

Specification varchar(45),

Date_Joined date,

foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)

);



create table Business_Customer(

ContactID int primary key identity(1,1),

CustomerTypeID as 2 persisted, --Business

Business_Name varchar(30)not null,

Business_Type varchar(30),    

foreign key (ContactID,CustomerTypeID) references Customers(ContactID,CustomerTypeID)

);   



 create procedure CustReg

    @custType varchar(11),  

    @custTypeID int,

    @firtName varchar(25),

    @lastName varchar(25),

    @dob date,

    @telephone varchar(18),

    @notes varchar(250),

    @addDate date 

    AS

    BEGIN



    SET NOCOUNT ON



    --SET IDENTITY_INSERT CustomerTypes ON



   INSERT INTO CustomerTypes values(@custType)

        SELECT 1,'Regular'union all

        select 2, 'Business'



           --SET @CustTypeID = SCOPE_IDENTITY()

          --SET IDENTITY_INSERT CustomerTypes OFF



           DECLARE @ContactID int



           INSERT INTO 

    Customers(CustomerTypeID,FirstName,LastName,DOB,Telephone,Notes,AddDate)

           VALUES(@CustTypeID,@firtName,@lastName,@dob,@telephone,@notes,@addDate)



           SET @ContactID = SCOPE_IDENTITY()

       end  





    exec CustRegistration 'Business','5','Jean','Bruno','2012-03-09','073098743','business trip','2017-07-04'

After running exec CustRegistrationthe Customer table is populated with 5 in CustomerTypeID column. I was excepting to get a warning message of foreign key conflict in table customerType. What am I doing wrong?

It looks possible that you have inserted more into the customer type table than you think via the CustReg proc as CustomerType is not unique and the proc does

INSERT INTO CustomerTypes values(@custType)

    ....

If I'm honest the model could do with some work as the alternate key of the customer table appears to contradict the primary key.

At the moment customer 1 and 2 could be the same customer of different types. That may sound like it makes sense but I'd suggest it is incorrect and allows for a many to one relationship between parent and child despite trying to enforce the type. In general super types should in general be unaware of subtypes.

As your question is around inheritance then it would follow that

A business customer is A customer

A regular customer is A customer

A customer is either A regular customer or A business customer or potentially both.

I'd remove the customer type table/enumeration and the calculated customer type columns and just use a Customer table and the two child tables.

The primary key of the child tables should be the 'inherited' customer ID i.e. the parent table should have a one to one relationship with any child table.

Use the fact that the customer ID exists or is in the child table to determine what type or types of customer they are.

If you must enforce that the customer can only be of one subtype this could potentially be done via an indexed view as a union of the customer ids in the child tables. That's a theory and not something I've tried.

Unrelated but notes about a customer would probably be better served as a separate table also.