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'
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.
Answered by Tomas Ingram on September 10, 2020
4 Asked on December 6, 2020 by ran
1 Asked on December 5, 2020 by aleksey-vitsko
1 Asked on December 3, 2020 by monya-feldman
1 Asked on December 3, 2020
2 Asked on December 3, 2020 by martyb
0 Asked on December 3, 2020 by raghu-mutyam
1 Asked on November 28, 2020 by spksmithy
1 Asked on November 14, 2020 by hasnain
1 Asked on November 7, 2020 by curiouschad
0 Asked on October 31, 2020 by jim-d
1 Asked on October 29, 2020 by patrick-kusebauch
1 Asked on October 12, 2020 by chris-di-carlo
0 Asked on October 10, 2020 by jonathan-hurdman
1 Asked on October 3, 2020 by deadmann
1 Asked on September 28, 2020 by exilevoid
0 Asked on September 25, 2020 by pgerrits
Get help from others!