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
Top of Page