Re: primary key - tag numbers

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 24 Feb 2003 09:18:42 -0800
Message-ID: <c0d87ec0.0302240918.2397b4be@posting.google.com>

>> 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. <<

Make the tag number and the date the primary key, as you suggested, but keep the date separate. I assume that there will be a history of the tag number -- the animal is tagged, then (placed in a home, put down, sold to a circus, etc.) and the tag goes back into the box of tags in the kennel.

CREATE TABLE History -- need better name! (tag_nbr INTEGER NOT NULL

         CHECK (tag BETWEEN 000000 AND 999999),  event_code INTEGER NOT NULL

           REFERENCES Events(event_code)
           ON UPDATE CASCADE,
 event_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              NOT NULL,

 ...,
 PRIMARY KEY (tag_nbr, event_date)); Received on Mon Feb 24 2003 - 11:18:42 CST