tables inheritance stored procedure

Database Administrators Asked by juclart ngouedi on September 10, 2020

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 


    --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 

           SET @ContactID = SCOPE_IDENTITY()

    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?

One Answer

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.

Answered by Tomas Ingram on September 10, 2020

Add your own answers!

Related Questions

Dbca hangs at 59%

0  Asked on December 6, 2020 by user133197


Performance issue in Postgresql 12

0  Asked on December 3, 2020 by raghu-mutyam


Linked Server throws metadata error

1  Asked on November 30, 2020 by cam


What is an ODBK file and how to restore that?

1  Asked on November 14, 2020 by hasnain


Calculating percentage over sub query SQL

1  Asked on October 29, 2020 by patrick-kusebauch


How to select the rows affected by an update

2  Asked on October 27, 2020 by enharmonic


Corruption in mariadb binlog

2  Asked on October 18, 2020 by ctutte


“SQL command not properly ended” with trigger

1  Asked on September 28, 2020 by exilevoid


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir