image: GUIDs
Close examination show the copying process has disrupted the GUIDs
R E L A T E D   C O N T E N T
ADVERTISEMENT

Hands on: GUID and bad

When uniqueness isn’t quite all it’s cut out to be

Mark Whitehorn, Personal Computer World 21 Aug 2007
ADVERTISEMENT

I heard recently from John Stevens, who uses an Access database to record departments and the people working in them.

He uses a GUID (Global Unique Identifier) field as the primary key in his tables of people and departments so he and his colleagues can enter records independently and combine their tables later without fear of primary key clashes.

The database contains two tables – one called Depts for departmental information and one called People. There is a one-to-many relationship between Depts and People: many people can work in one department and each person can only work in one department at a time.

John took copies of the two tables by copying the structure only (by highlighting the table name, clicking the Copy button and the Paste button on the menu bar, and choosing the Structure Only option). He copied the records from the originals into the new tables by copying and pasting.

He then tried to establish the one-to-many join between the new tables, but Access reported that data in the copy of the People table, called PeopleCopy, “violates referential integrity rules”.

When is a copy not a copy?
The data should be a direct copy of each table. The screenshot (above left) shows the original Depts table and the copy, DeptsCopy. The copying process has failed to work in the GUID field called DeptID. All the GUIDs, which are acting as primary key values, have been changed, thus violating the referential integrity constraints.

Here’s a little background on GUID fields. In Access, the data type Number can have various field size settings, one of which is Replication ID. This field size can also be used with the Autonumber data type. A Replication ID is Access’ equivalent of a GUID and is designed to hold a string with a high probability of being unique. They are mostly used as primary key fields, where several people are entering records into different copies of a database. The guarantee of uniqueness means that when the copies are brought together, problems caused by records having the same primary key are unlikely to occur.

When designing a table that uses GUIDs, most users choose the Autonumber/Replication ID setting. This lets Access generate the IDs automatically: they’re made up from blocks of mixed letters and numbers, separated by dashes and wrapped in curly brackets. They do the job, but they’re a nightmare viewed from a human angle because they don’t have any instantly recognisable meaning.

Their unwieldiness also extends to their behaviour, which can vary with the different copying processes that can be used with the autonumber version. John’s problem is a good example: copying rows using cut and paste from one table to another causes the GUIDs to be regenerated. In this case the behaviour is exactly what John didn’t want, but we could argue that it is reasonable because this is not only a GUID, it is also an autonumber. The job of an autonumber field is to generate new, unique numbers, so that is what it is doing. This sounds reasonable at first sight, but it unravels after a little more experimentation.

If you repeat the experiment but use an Append query to add rows from the original table to the new one (copied as the structure only), the GUIDs are transferred in perfect condition. And if you create a new table by copying both the structure and the data from the original table, the GUIDs are again copied across without alteration, even though the new field is still an autonumber.


All Software Applications

Like this story? Spread the news by clicking below:

Post this to Delicious del.icio.us    Post this to Digg Digg this    Post this to reddit reddit!

Permalink for this story
M A R K E T P L A C E
Get your free demo of Numara Track-It! 8 - the leading help desk solution for IT related issues.
Make presentations, review documents & share your entire desktop. 30-day free trial! (cc required).
Discover how remote support can fuel your IT business in ways you've never thought of before.
Apply ITIL best practices at your service desk while eliminating integration cost. Learn more here.
WAN based, automated, daily vulnerability assessments. Click here to try and request our whitepapers.
Have your product or service listed here >   
Sponsored links
F E A T U R E D   J O B S
| Aston Carter
C++ Market Data Developer - Unix - Java - SQL My client are a top financial software house based in the city. They are currently seeking a C++ developer to work in their market data ... more >
| Aston Carter
Junior Python Developer - SQL - Functional Programming - Javascript - Django - Perl - Ruby - MVC My client are a startup software house that specialise in online gaming. The team is small and ... more >
| Aston Carter
Required: Salesforce CRM, Excel My client are Britain's leading financial spread betting firm and are the world's leading trading platform for private investors. This is an excellent opportunity for Salesforce Administrator to join a leading ... more >
| Aston Carter
C# Developer, Hedge Fund, Algo Trading, London The role is working for a boutique derivatives trading company who focus on options high frequency algorithmic trading. They are looking for a bright junior Microsoft .net developer ... more >
More job opportunities