Combination of date column and other colum as primary
whizkid, September 11, 2005 - 9:20 am UTC
hi tom,
we have a employee attendance table which has two columns emp_code and attendance_date. the col att_date stores the sysdate with time (business requirement - time is needed). how do we put a constraint for marking attendance only once for each emp_code in the table?
emp_code + trunc(atten_date) should be unique
thanks,
September 11, 2005 - 10:25 am UTC
create unique index once_a_day_only on tab(empno, trunc(atten_date) );
Why not this : check( dt=trunc(dt));
Jagjeet Singh, September 11, 2005 - 1:36 pm UTC
Why not this : check( dt=trunc(dt));
September 11, 2005 - 6:20 pm UTC
because the older I get, the less I like relying on implict conversions and defaults.
I type fast, I don't mind typing more keystrokes to be explicit. I've gotten in the habit of just filling in the blanks.
How to set primary key for a date data field
S Z Ziaudeen, September 11, 2005 - 11:58 pm UTC
Dear Tom,
Thanks for your Timely Help.
I tried as what you said.
Now I am getting the unique constraint message while trying to Insert duplicate records.
Thank you very much Tom
regards,
S Z Ziaudeen
Timestamp and Date Columns in Primary Keys
John Gilmore, July 17, 2006 - 6:24 am UTC
Hi Tom,
I was wondering if you had an opinion on whether primary keys should contain date or timestamp columns.
In the past I've heard of people stating that dates should never be used as PK columns; however, I've recently heard of a DBA rejecting the design of a table because the composite primary key contained a timestamp column. I believe his reasoning was to do with the fact that it's harder to accurately specify the value of that column.
Do you have any thoughts on this?
July 17, 2006 - 2:44 pm UTC
if the DATE/TIMESTAMP is part of the natural key - sure, why not?
as a surrogate key - no, not unique enough. use sequences for surrogates.
Because of performance apparently ...
John Gilmore, July 18, 2006 - 3:21 am UTC
I've since learnt that his justification for not using dates and timestamps in primary keys is due to performance.
He states that dates and timestamps both undergo internal transformations which have a negative impact on performance when used as join columns. Even varchars should be avoided in primary keys and any kind of composite key is also bad. Any of these are valid justification for the introduction of a surrogate primary key.
I'm very hesitant to accept any of that advice. I suspect there would be a minor, usually negligible, difference when joining on a two-column composite key when compared with a numeric surrogate key but I find the statements about date/timestamp, and especially varchar, keys to be a bit far fetched.
July 18, 2006 - 8:21 am UTC
he would be "wrong"
a date is a 7 byte binary field
a timestamp is a 11'ish byte binary field
does the DBA realize that Oracle numbers are in effect varying length "character strings" of 0 to 22 bytes?
do they realize that in actuality - it is more efficient to compare two fixed width 7 byte dates than it would be to compare two arbitrary numbers?
varchars are just varying length character strings as well.
ask DBA for facts, not suppositions. If dates are "bad for joins", well, then they should be able to SHOW THAT CONCLUSIVELY.
create table t
as
select rownum id, sysdate+rownum dt, to_char(rownum) str
from all_objects;
alter table t cache;
alter session set sql_trace=true;
set autotrace traceonly
select * from t;
select * from t t1, t t2 where t1.id = t2.id;
select * from t t1, t t2 where t1.dt = t2.dt;
select * from t t1, t t2 where t1.str = t2.str;
set autotrace off
select * from t t1, t t2 where t1.id = t2.id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3322 0.28 0.29 75 3645 0 49815
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3324 0.29 0.29 75 3647 0 49815
********************************************************************************
select * from t t1, t t2 where t1.dt = t2.dt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3322 0.30 0.30 75 3645 0 49815
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3324 0.30 0.30 75 3647 0 49815
********************************************************************************
select * from t t1, t t2 where t1.str = t2.str
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3322 0.25 0.26 75 3645 0 49815
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3324 0.26 0.27 75 3647 0 49815
Looks pretty much the "same" doesn't it?
Now, if your natural key was 10 attributes wide - there might be an argument for using a surrogate key....
But if the natural key is a string - use it.
if the natural key is a date - USE IT.
if the natural key is 50 attributes - surrogates might make sense.
but don't say "dates are slow to join", it just isn't so.