Foreign keys are normally straight forwards to use, but there are some very subtle differences between how these work in SQL Server as compared to Access.

Composite foreign keys in databases

After around 20 years working with databases it is unusual to have to go back to basics but then databases are complicated beasts and I certainly don’t have a copy of the ANSI standards on my book shelf with which to regularly consult. I suppose like a lot of developers I have been drawn to databases through using highly productive database RAD tools like Access, and tended to accept what it does as ‘the standard’, and then gradually revise my understanding of ‘the standard’ as I spent more time with database products like SQL Server, but after working with SQL server for ten years, this gap in my knowledge gave me a bit of a shock when the keyword MATCH crossed my desk.

Top of Page

An example of composite keys

Using composite keys in a database is controversial, the advantage being that you save potentially on having to create subsequent joins to pull information which otherwise would not form part of the key, and the downside being that the keys propagate and this generates overhead in storage and reduces retrieval speed; as opposed to using a pure pseudo numerical automatic number, which yields better performance but carries less meaning and potentially requires more joins in the application.

So let us get things into perspective by considering a simple example, here the composite primary key in tblContact is propagated as a foreign key in tblContactAction.

In contrast, shown below is the same relationship created in Access which has a particular nice graphical presentation of composite keys.

Top of Page

Partially incomplete foreign keys

I want now to turn our attention to what happens when a foreign key is only partially completed. What are the obligations on the database with regards to allowing a partially complete key to be saved, and at what point should rules be applied to validate the completion of the whole key. At this point you may be either nodding your head, already fully understanding MATCH, or wondering if I understand the word referential integrity. Indeed I can be even be more extreme, what happens if you enter invalid data as part of the incomplete key; that is to say entering an invalid CustomerId in tblContactAction but not entering any value in the contactname part of the key. If the database is not going to validate the entire key at this point, should it not then allow anything to be entered as part of an incomplete composite key?

Below is some sample data to illustrate this issue.

These are linked tables in Access to SQL Server, and you can see that for ActionId number 6, as long as the ContactName is left incomplete, SQL Server allows an apparently illegal value of 999999 for the customerid. Also for ActionId number 2 SQL Server allows a null in the contactname.

A strange feature here is that if I build the tables in Access and repeat the above I will not be allowed the entry either of incomplete or invalid data values as part of the composite key. Attempting to leave a null in the contactname results in the following warning.

Top of Page

Stand back for ANSI SQL

At this point it appeared to me that SQL Server was somehow violating referential integrity, but one of our customers Gillian MacKenzie from BizEnergy got her teeth dug into the problem, and enlightened me on the contents of that dusty book on ANSI SQL.

A little digging into SQL92 reveals that the foreign key syntax also supports a MATCH option as shown below.

<references specification> ::= REFERENCES <referenced table and columns> [ MATCH <<match type> ] [ <referential triggered action> ]
<match type> ::= FULL | PARTIAL | SIMPLE

FULL means that no part of the key can contain a null unless all other parts of the key contain null, and otherwise all parts contain valid values (this is how Access behaves). PARTIAL allows for some parts of the key to contain a null, but other parts must contain valid values, and SIMPLE, which is the default, states that either one of the columns contains null (in which case data content in the others is not specified) or all the columns contain valid values (this is how SQL Server behaves in agreement with the requirements of the SQL-92 standard).

Top of Page

An essential point to consider

The result of this investigation other than pointing out issues to be aware of when using composite foreign keys, is that when an application is translated from Access to SQL Server, an additional check should be added to ensure that when completing foreign key fields, the full key is always entered to force SQL server validation and provide compatibility with existing application behaviour.

As a resolution to our problem, the following check could be added,

ALTER TABLE [dbo].[tblContactAction]
ADD CONSTRAINT [chkIncompleteKey]
CHECK (([CustomerId] IS NULL AND [Contactname] IS NULL)
OR ([CustomerId] IS NOT NULL AND [Contactname] IS NOT NULL))

This all goes to show, that no matter how long you work with database there are always surprises in store. My thanks to our customer Gillian Mackenzie at BizEnergy Limited who first drew the issue of MATCH to our attention when upsizing databases to SQL Server using our product.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz