SQL Logical Key

Question

I've a three tables let's say tableA, tableB, tableC. The relations ship of these tables are :

• tableA to tableB one to many respectively
• tableB to tableC one to many respectively

tableA :

CREATE TABLE tableA (
id int Not Null,
name varchar(50) Not Null,
Area varchar(20) Not Null,
CONSTRAINT PK_tableA PRIMARY KEY NONCLUSTERED(id Asc) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
)

tableB:

CREATE TABLE tableB (
id int Not Null,
appNbr int NOT NULL,
appCode char(8) NOT NULL,
tableAId int Not Null,
beginDt datetime Not Null,
EndDt datetime Not Null,
updatedDt datetime Not Null,
CONSTRAINT PK_tableB PRIMARY KEY NONCLUSTERED(
id Asc,
appNbr ASC,
appCode ASC,
tableAid ASC,
beginDt ASC,
endDt ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
) 
GO

ALTER TABLE tableB  WITH CHECK ADD  CONSTRAINT FK_tableB_tableAId FOREIGN KEY(tableAId)
REFERENCES tableA (Id)
GO

ALTER TABLE tableB CHECK CONSTRAINT FK_tableB_tableAId
GO

tableC:

CREATE TABLE tableC (
id int Not Null,
tableBId int Not Null,
beginDt datetime Not Null,
endDt datetime Not Null,
indicator char(1) Not Null,
contactId int Not Null
UpdateDt datetime Not Null,
CONSTRAINT PK_tableC PRIMARY KEY NONCLUSTERED(
id ASC,
tableBId Asc,
beginDt ASC,
endDt ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
)

ALTER TABLE tableC  WITH CHECK ADD  CONSTRAINT FK_tableC_tableBId FOREIGN KEY(tableBId)
REFERENCES tableB (Id)
GO

ALTER TABLE tableC CHECK CONSTRAINT FK_tableC_tableBId
GO

The problem here is when I run script to create table C it shows error as below

There are no primary or candidate keys in the referenced table
   'dbo.tableB' that match the referencing column list in the
   foreign key 'FK_tableC_tableBId'.  
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

after researching on google I came to know that it is not allowed to create a foriegn key out from composite primary key. So, I have decided to make id as my primary key and remaining fields(appNbr, appCode, tableAid, beginDt and endDt) into logical key on table B. I've read some online material on logical key, but not able to understand.

Can anyone please explain me what is the logical key and how can I use it in my example.

Answer 1

Generally speaking, logical keys are anything that define relationships between data and tables. Primary keys and foreign keys are the most common examples, though unique and composite indexes fall under the same umbrella. You have to have a PK or unique index that is an exact match of any FK you define.

It sounds like you understand the issue and how to fix it, but here are two ways:

• Add a unique index on tableB (id):

  CREATE UNIQUE INDEX UI_bID ON dbo.tableB (id)

• Change your PK on tableB to only cover (id):

Ex:

CREATE TABLE tableB (
id int Not Null,
appNbr int NOT NULL,
appCode char(8) NOT NULL,
tableAId int Not Null,
beginDt datetime Not Null,
EndDt datetime Not Null,
updatedDt datetime Not Null,
CONSTRAINT PK_tableB PRIMARY KEY NONCLUSTERED(
id Asc
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
) 

Then include the other columns in a unique index:

CREATE UNIQUE INDEX UI_tableB ON dbo.tableB (
appNbr ASC,
appCode ASC,
tableAid ASC,
beginDt ASC,
endDt ASC)

Answer 2

TableB.id is not guaranteed to be unique, which is one of the requirements for a foreign key reference. So, either you use id as TableB's primary key CONSTRAINT PK_tableB PRIMARY KEY NONCLUSTERED(id), or you reference all of the PK_tableB candidate columns CONSTRAINT FK_tableC_tableBId FOREIGN KEY(tableBId/*... and a column for each referenced column*/) REFERENCES tableB (id, appNbr, appCode, tableAid, beginDt, endDt).