Re: primary key - tag numbers

From: Bernard Peek <>
Date: Sun, 23 Feb 2003 23:11:36 +0000
Message-ID: <s$>

In message <>, tretasdehr <> writes
>In a database of animals at a large shelter, the tag number of the
>animals can be large, but it will still need to be repeated after
>about 2 years. That is, the tag number will be used again.
>If the tag number is to be used as the primary key in a (SQL) database
>that has to preserve its records for legal reasons, what is the best
>way to keep the primary key unique and prevent it from repeating with
>the re-use of the tag number?

It can't be the primary key in a table unless you purge the record with the same number before you create the new one.

You could create a working table that never has two year old data, you could keep 1 year's data and write older data into a history table. The history table can have other fields (such as the year the tag was created) as a compound key.

>Could it just be prefixed by the last 2-3 years of the date, eg 02---
>or 002--- ? Since that would be tedious to write every time, can this
>year information prefix be entered automatically in a SQL database, so
>that all the user needs to do is to enter the actual tag number? That
>is, if the user enters a number like 40023 then in the actual
>database, that is recorded as 00240023. However, when it is recalled,
>it is just shown as 40023.

That's perfectly possible. I find that it's often a good idea to record the precise date and time each record is created. It helps with debugging. If it's possible then you may like to do that.

Bernard Peek SF & Computing book reviews and more.....

In search of cognoscenti
Received on Mon Feb 24 2003 - 00:11:36 CET

Original text of this message