Avoiding duplicate records - Acc97

[dawnecia]

I have a database where users can only create one record per day in a table.
The table is constructed with ID as primary key, UserID, Date, totalTime etc.
How do I get the table to only accept one entry per UserID per Date?

 

[S Panja]

Define a secondary unique key on UserID + Date.

 

[Vincent Johns]

Define a secondary unique key on UserID + Date.

That would work, but I think it's not necessary, and would occupy space
in the table. Just create a new index on those 2 fields, being sure
that the Date field doesn't include time of day as well, and set the
index to "No Duplicates".

 

[peregenem]

That would work, but I think it's not necessary, and would occupy space
in the table. Just create a new index on those 2 fields, being sure
that the Date field doesn't include time of day as well, and set the
index to "No Duplicates".

You seem to be recommending a unique index over a unique constraint on
ground of 'space in the table'

i.e. given

CREATE TABLE Table1 (key_col INTEGER NOT NULL)

you say no to

ALTER TABLE Table1 ADD UNIQUE (key_col)

and yes to

CREATE INDEX UNQIUE ON Table1 (key_col)

What are your reasons?

Why not replace the redundant PRIMARY KEY?

 

[Tim Ferguson]

I have a database where users can only create one record per day in a
table. The table is constructed with ID as primary key, UserID, Date,
totalTime etc.
How do I get the table to only accept one entry per UserID per Date?

I would be making a compound primary key out of the (UserID, SomethingDate)
pair. Just for good luck, I would pack the date into a Long Integer field,
partly because it's probably physically quicker and neater, more because it
logically removes the possibility of confounding by stray time values
creeping in.

Did you know that you can put a Long Date format (or whatever) on a long
integer field?

Hope that helps


Tim F

 

[peregenem]

I would be making a compound primary key out of the (UserID, SomethingDate)
pair.

Me too.

Just for good luck, I would pack the date into a Long Integer field,
partly because it's probably physically quicker and neater, more because it
logically removes the possibility of confounding by stray time values
creeping in.

Did you know that you can put a Long Date format (or whatever) on a long
integer field?

If I have temporal data then the chances are I want to operate on them
using temporal functions. Formatting a different data type in
individual front end applications isn't going to do it for me if I'm
always having to cast as DATETIME in my SQL code

Instead, I'd strongly type as DATETIME and use CHECKs to ensure my
start date and end dates have the minimum and maximum possible time
values respectively e.g.

CREATE TABLE Test (
key_col INTEGER NOT NULL,
start_date DATETIME DEFAULT DATE() NOT NULL,
CONSTRAINT start_date_value_valid CHECK
(
HOUR(start_date) = 0
AND MINUTE(start_date) = 0
AND SECOND(start_date) = 0
),
end_date DATETIME,
CONSTRAINT end_date_value_valid CHECK
(
HOUR(end_date) = 23
AND MINUTE(end_date) = 59
AND SECOND(end_date) = 59
),
PRIMARY KEY (start_date, key_col)
);

The above wouldn't work for a Jet 3.51 database, though, but then I
wouldn't use one of those either ;-)