Would a Check Constraint be prefereable to a Referential Constraint in this case?

I have a table that I need to perform quickly. It has 6 partitions and needs to load 10M rows a day as quickly as possible. I have been asked to add two referential constraints to the table to assert the two fields have values that fall in a particular set of values. Is it possible that it would be faster to use a check constraint to ensure the new value was one of the set of 30 available values rather than a foreign key, or would the foreign key be faster?


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you can't use a check constraint to do referential integrity.

so it's not a matter of speed.
I guess if you want to hardcode the 30 values into a check constraint you "could" do that, and it will likely be faster for inserts
you'd have to try it both ways to confirm.

However, it's not a very robust solution since you won't "really" have referential integrity, only list-integrity.  If the constraining table changes your list constraint won't be accurate

Furthermore,  the existence of  foreign keys acts as metadata for the optimizer,  so, even if your inserts become faster
using that data in queries once the table is populated might be slower.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gswitzAuthor Commented:
I only need list integrity. The dimension table that is being created only has one column with a list of the values that I would hardcode into the check constraint. The constraining table exists only for the purpose of constraining the values for the other table. Rather than modifying the dimension table, we would just modify the check constraint. Yes, in this case I would be only trying to optimize the speed of INSERTs not SELECTs.

I'll do the testing and see how it effects the speed.

Thanks, sd.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

if list integrity is all you really need, then check constraint probably makes semantic sense as well as being slightly more efficient (but confirm that performance for yourself, don't trust me)
HainKurtSr. System AnalystCommented:
or create a small table

lookup (id, name, description)

and a table with referential integrity to this lookup table


where mytable.lookupid is fk to lookup.id

maintenance will be easy on this one... also you have description and other columns on lookup table so you can use this table in your app to list the options...
check previous posts.

the asker already has a lookup table, the question is whether a check constraint could work faster than using fk to the lookup
Christoffer SwanströmPartnerCommented:
I think a more important aspect than choosing between a constraint and a foreign key is to disable or drop the contraint/fk before loading new data and then enabling/recreating it only after the data has been loaded. This will make a a big difference regardless of which method you are using.
gswitzAuthor Commented:
Dropping the constraint is an interesting point to mention and I appreciate it, but it actually isn't a choice. There are multiple applications inserting heavily in a short window and dropping the constraint would defeat the point of it.

I appreciate everyone's help. I'm going with the FKeys until it is known that performance requirements are not being met.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.