Is it a good idea to duplicate columns across tables in order to enforce check constraints?

Question

I'm faced with a situation where I have two tables: A and B. B has a foreign key to A.

A has a column "Detailed" which identifies whether or not its children in B require their "Details" section to be filled out.

If I have my lean structure there is no way for me to determine if a record in B needs to have its "Details" section filled out, i.e. not null, without joining to A. Thus, the only way for me to prevent somebody from inserting or updating these records to an invalid state is to have a trigger to join with A and check its "Detailed" column.

My feeling is that constraints are better than triggers, as they are more like facts about data, in addition to filters, whereas triggers are only filters.

I could get by this by duplicating the "Detailed" column in B and then having a check constraint (Detailed = 'Y' AND Details IS NOT NULL) OR (Detailed = 'N')

Thoughts on the best way to approach this?

Answer 1

I would enforce a rule like this in the UI. If your business rules become more complicated, you are going to have your hands full with lots of redundant columns in your model in order to enforce all rules in the database schema.

Answer 2

All the tools you mentioned (constraints and triggers) are just a way to enforce the data consistency in the database.

Simple business rules, like "always having a reference", "not having a NULL" etc are enforceable with the constraints.

More complex business rules, like the one you mention here, should be enforced using triggers.

Constraints are not "better" or "worse" than triggers: they are just a shortcut for the rules you need to implement often.

For your task, just implement a trigger.

However, in Oracle, both constraints and triggers are implemented not in pure set-based way. They are called in a loop for each record affected by a DML operation.

Most efficient way would be creating a package that would serve as a single entry point to all DML against your table and check the Details in that package.

Answer 3

You're right to enforce this on the database level and Quassnoi's points are all good. In addition, you might want to investigate having the API for this operation reference an updatable join view of the two tables and implement the constraint through that.

Answer 4

In an ideal world, Oracle and other DBMSs would support "assertions":

create assertion no_more_than_50_per_user as
check(not exists(select null
                 from a join b on ...
                 where a.detailed = 'Y'
                 and b.details is null
);

They don't though (and not without good reason: it would be very hard to implement them in a performance manner!)

As Quassnoi suggests, triggers can be used instead - but you need to be aware of the dangers in a multi-user environment. To be sure of enforcing consistency you need to take out locks when checking data to ensure that this doesn't happen:

(Assume A record 1 currently has detailed='N', but all associated B records have details not null).

user1> Update A set detailed = 'Y' where a_id=1;

That works, because all the associated B rows have details not null.

user2> Update B set details = null where a_id=1;

That works, because user1 hasn't committed yet, so user2's trigger sees detailed='N'.

user1> commit;
user2> commit;

Now you have corrupt data. To prevent that, the trigger on B needs to select the A row "for update".

Answer 5

If the DETAILED field was to be duplicated on B you could use the foreign key to enforce it, e.g. (B.KEYFIELD, B.DETAILED) REFERENCES (A.KEYFIELD, A.DETAILED). I'm not crazy about duplicating the field on B, but on the other hand it seems that you have data which is related to B which exists on A. If you could remove DETAILED from A and push it down to B the design might be cleaner. Some additional details might help.

Share and enjoy.