Database Design - How to enforce a foreign key constraint on a table with a Surrogate Key

Question

My table schema is something like this

1. Master table : Clause

Columns

1. ClauseID = surrogate pk (identity)
2. ClauseCode = nvarchar user specified value
3. Class = nvarchar FK to a master class table
4. etc...

ClauseCode + Class = candidate key for this table

2. Master table : GroupClause

Columns

1. GroupClauseID = surrogate pk (identity)
2. GroupClauseCode = nvarchar user specified value
3. Class = nvarchar FK to a master class table etc...

GroupClauseCode + Class = candidate key for this table

3. Transaction / Mapping table :: GroupClause_Clause_Mapping : this table maps each group clause to multiple individual clauses

Columns

1. GroupClauseID = FK to GroupClause PK
2. ClauseID = FK to Clause PK
3. etc...

Requirement : Each Group clauses can only be mapped to clauses belonging to the same class as itself

Issue : This above table design does not enforce that requirement at a DB level.

One possible solution : Table *GroupClause_Clause_Mapping* has columns

1. ClauseCode
2. GroupClauseCode
3. Class

wherein i can create ClauseCode + Class as FK to clause table as well as GroupClauseCode + Class as FK to GroupClause table.

However, if i do it this way, then the surrogate identity keys are useless and i might as well get rid of them.

Is there an issue with my design using the surrogate keys?

Any suggestions as to how i can use the surrogate keys and still enforce my constraint at a DB level?

Answer 1

"if i do it this way, then the surrogate identity keys are useless and i might as well get rid of them."

That's correct. This is one reason for using the natural keys instead of the surrogate: when you need to implement some additional constraint or logic using the natural key values.

You might want to reference the surrogate from other tables so the surrogate keys still could be useful. If you don't use the surrogate at all then it's best to drop it. Surrogate keys usually imply a certain overhead because they are typically indexed, which can hurt the performance of inserts.

Answer 2

If the Codes themselves are large/unwieldy, then you may still want to use the surrogates, where available.

Since you only want to enforce the classes matching, then your mapping table could be

ClauseID,
GroupClauseID,
Class (or possibly ClassID)

For your master tables, you'd still have PK (ClauseID), and a unique constraint (ClauseID, Class). You can then decide whether to just FK (ClauseID,Class) or to have two FKs between the mapping table and each master table (effectively, you'd then be saying that one FK is the foreign key reference, and the other is there to enforce your rules).

I've got a similar setup in one of my databases (think survey system):

CREATE TABLE [dbo].[DataItems](
    [DataItemID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [TypeRequired] [varchar](10) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    /* Other Columns */
 CONSTRAINT [PK_DataItems] PRIMARY KEY NONCLUSTERED 
(
    [DataItemID] ASC
),
 CONSTRAINT [UX_DataItems_ClientAnswerFKTarget] UNIQUE CLUSTERED 
(
    [DataItemID] ASC,
    [TypeRequired] ASC
),
 CONSTRAINT [UX_DataItems_Name] UNIQUE NONCLUSTERED 
(
    [Name] ASC
)
)

CREATE TABLE [dbo].[ClientAnswers](
    [ClientAnswersID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ClientID] [uniqueidentifier] NOT NULL,
    [DataItemID] [uniqueidentifier] NOT NULL,
    [TypeRequired] [varchar](10) NOT NULL,
    [BoolValue] [bit] NULL,
    [IntValue] [int] NULL,
    [CharValue] [varchar](6500) NULL,
    [CurrencyValue] [int] NULL,
    [DateValue] [datetime] NULL,
    /* Other Columns */
 CONSTRAINT [PK_ClientAnswers] PRIMARY KEY CLUSTERED 
(
    [ClientID] ASC,
    [DataItemID] ASC
)
)
GO
ALTER TABLE [dbo].[ClientAnswers] ADD  CONSTRAINT [FK_ClientAnswers_DataItems] FOREIGN KEY([DataItemID],)
REFERENCES [dbo].[DataItems] ([DataItemID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[ClientAnswers] ADD  CONSTRAINT [FK_ClientAnswers_DataItems_TypesMatch] FOREIGN KEY([DataItemID],TypeRequired)
REFERENCES [dbo].[DataItems] ([DataItemID],TypeRequired)
GO

And then I go further and have more constraints ensuring that the type column matches the non-null *Value column