Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Data Warehouse and Constraints
In article <882370395.2082996227_at_dejanews.com>, <shawe_at_indy.tce.com> wrote:
>We are creating a data warehouse and it has been suggested that we do
>not turn on foreign key constraints after loading data.
>
>Currently, we drop the foreign key constraints, SQLLOAD data into our
>tables and then turn on the foreign key constraints. If there is a
>problem with the constraint we then look into why constraint was unable
>to be turned on.
>
>Due to the fact that if we turn on the foreign key constraints and there
>is not an entry in the parent table the job would fail thus holding up
>our schedule, It has been suggested that we should run a SQL script that
>checks for integrity violations and if there is a row missing in a parent
>table that row would be added with a default value.
>
>How is everyone else handling these constraint situations?
>
>Any suggestions would be appreciated
It really depends on the data. There are some times where the data was orphaned during a delete and should not be loaded. There are other times where the data was orphaned by mistake and it should be either reported or loaded.
Recently I have found that the new frontend tools are a great way to demonstrate bad data to executives that can easily persuade operational folks to correct their data.
So my suggestion is to determine if the data is good or not, then let the operational folks have an opportunity to correct it and then finally load it with a default of ERROR or UNKNOWN if it remains uncorrected.
mjr Received on Mon Dec 22 1997 - 00:00:00 CST