Hi,

In the docs, there is a sentence that says:

Don't use surrogate keys on the subtype tables, it's important the PK of the subtype tables has the foreign key to the supertype's PK.

Can someone please help me understand what this means?

I am a bit confused.

Does it mean that the child (subtype?) table should not have its own primary key, along with the foreign key that points to the parent (supertype?)?

And if so, why?

Thank you very much, Mike

It's like this: you have a customer table, and you give it an identity PK field: CustomerID. You also use a surrogate key field, CustomerKey, which is a field with a unique constraint and contains a short string and THAT field is for example used as the PK side of an FK constraint in the DB (instead of CustomerID).

Otis wrote:

It's like this: you have a customer table, and you give it an identity PK field: CustomerID. You also use a surrogate key field, CustomerKey, which is a field with a unique constraint and contains a short string and THAT field is for example used as the PK side of an FK constraint in the DB (instead of CustomerID).

Ahh, so then the subtype table should have its own PK, along with a FK related to the supertypes PK?

Thanks, Mike

MikeG wrote:

Ahh, so then the subtype table should have its own PK, along with a FK related to the supertypes PK?

Yeah. See, here is a typical DB structure for a valid inheritance:

-- Employee table

CREATE TABLE [dbo].[Employee](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [StartDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC
) ON [PRIMARY],
 CONSTRAINT [IX_Employee] UNIQUE NONCLUSTERED 
(
    [Name] ASC
) ON [PRIMARY]
) ON [PRIMARY]


-- Clerk table, will inherit from Employee

CREATE TABLE [dbo].[Clerk](
    [ClerkID] [int] NOT NULL,
    [JobDescription] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Clerk] PRIMARY KEY CLUSTERED 
(
    [ClerkID] ASC
) ON [PRIMARY]
) ON [PRIMARY]


-- set the relation Clerck -> Employee

ALTER TABLE [dbo].[Clerk]  WITH CHECK ADD  CONSTRAINT [FK_Clerk_Employee] FOREIGN KEY([ClerkID])
REFERENCES [dbo].[Employee] ([EmployeeID])

As you see, Clerk table (subtype) doesn't have a surrogate key, as this key have to be related to EmployeeID.

Hope helpful

Thank you for this!

Does this mean that the ClerkID in the Clerk table will always be an identical match to the EmployeeID in the Employee table?

For example, if I have a record in the Employee table which has an EmployeeID of 5, and then created a Clerk record, would the ClerkID also be 5? Is that the intention?

Is this also a valid way:

Employee Table:

EmployeeID PK int autonumber FirstName varchar Unique

Clerk Table:

ClerkID PK int autonumber EmployeeID FK (to Employee table) int JobDesc varchar....

Im wondering because I have never used the form where the ClerkID would be the same as the EmployeeID and a bit confused by it.

Thank you, Mike

MikeG wrote:

Does this mean that the ClerkID in the Clerk table will always be an identical match to the EmployeeID in the Employee table?

Exactly.

MikeG wrote:

For example, if I have a record in the Employee table which has an EmployeeID of 5, and then created a Clerk record, would the ClerkID also be 5? Is that the intention?

Yes.

MikeG wrote:

Is this also a valid way:

Employee Table:

EmployeeID PK int autonumber FirstName varchar Unique

Clerk Table:

ClerkID PK int autonumber EmployeeID FK (to Employee table) int JobDesc varchar....

This would create a 1:m relation between Employee and Clerk. This isn't the way the inheritance works. If there's a 1:m relation, two clerks could point to the same EmployeeID (John Smith, f.i.). You would say: then lets add a UC (Unique Constraint to Clerk.EmployeeID). Well, that isn't good as well.

The idea is that Clerk inherits from Employee, including its PK, so at code you could do something like:

ClerkEntity myClerk = new ClerkEntity(someClerkIdSoEmployeeId);
EmployeeEntity myClerk = new ClerkEntity(someClerkIdSoEmployeeId);

I you have two fields that unique identify Clerk (EmployeeID and ClerkID) that doesn't make sense to the inheritance approach.

Hope this was helpful

Hi David!

Yes, yes, now I undertand

1:1 relationships in databases is a new concept for me, and I very much appreciate you helping me understand it. I created the tables in the DB and saw it work, and this opens up many possibilities.

Thank you very much for your help David!!!!

Cheers, Mike