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_at_posting.google.com>
...,
PRIMARY KEY (tag_nbr, event_date)); Received on Mon Feb 24 2003 - 18:18:42 CET
Date: 24 Feb 2003 09:18:42 -0800
Message-ID: <c0d87ec0.0302240918.2397b4be_at_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 - 18:18:42 CET