Maintain Referential Integrity between tables

[barnard90]

I am having two tables . A Master table and a detail table
The master table is a HOLIDAY table which has a list of all the holidays in the company. The field is HOLIDAY_DATE.


There is a detail table is HOLIDAY_PROCESS table which contains the overtime information worked in the holidays . The field is HOLIDAY_WORKED. I need a referential integrity between HOLIDAY_WORKED and HOLIDAY_DATE.

But the HOLIDAY_DATE in HOLIDAY table is having DATE as the datatype and HOLIDAY_WORKED in the HOLIDAY_PROCESS table is maintaining the data type as DATETIME.

The master table maintains only dates.( Eg:- 1-JAN-2000 )

The detail table maintains date
and time. (Eg:- 1-JAN-2000 2:30:33 AM )

Can I still maintain a Referential Integrity check. Please suggest .

 

[SantaMufasa]

Barnard,

Please confirm the name of the PRIMARY KEY column in your HOLIDAY table. It is a poor design to use HOLIDAY_DATE as a PRIMARY KEY...you should have a simple Oracle sequence providing a strictly NUMERIC unique identity for each holiday, then have a numeric (not DATE) FOREIGN KEY definition in the HOLIDAY_PROCESS table that points to the matching unique numeric identify in the HOLIDAY table.

Let us know your findings and your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]