asked on

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?

Thanks,

G

Sean Stuber

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

so it's not a matter of speed.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gswitz

ASKER

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.

Sean Stuber

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)

HainKurt

or create a small table

lookup (id, name, description)

and a table with referential integrity to this lookup table

myTable(id,....,lookupid)

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...

Sean Stuber

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

SOLUTION

Christoffer Swanström

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gswitz

ASKER

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.

Thanks,

G