Foreign Key Issue

[stanky23]

If you look at the properties of a foreign key relationship in a diagram there is a check box for "Enforce Relationship for Inserts and Updates."When I do a CREATE TABLE and add a foreign key constraint I want to have this box unchecked. In my Create I include ON UPDATE NO ACTION, ON DELETE NO ACTION, NOT FOR REPLICATION but this box remains checked. Is there a way to make this unchecked during a CREATE TABLE or with an ALTER?

 

[cdukes]

Why do you want this unchecked?

The whole point in foreign keys is to main integrity within your database. By saying that you do not want to check the foreign keys, there will be no point in creating the relationship in the first place.

Hope this helps,

Chris Dukes

 

[stanky23]

Yeah, that was obvious to me...Why would I want to create a foreign key relationship that doesn't enforce replication. As it relates to query performance my query is much more efficient with the foreign key established with this box unchecked. I'm obviously violating textbook normalization rules but the increase is noticable. At the same time the application allows you to manually remove this option. Dunno...

--Ryan

 

[cdukes]

Yes,
Your performance will be increased as it does not have to check the foreign key, but what is more important for the users. performance or data integrity?

Having worked on many databases, I would prefer the data to be clean than have a fast queries.

Consider the following:

Users have to enter a country in text format. The text has no foreign key. Thus for Great Britain the user might enter:
GB, G.B., Great Britain, Britain, UK, U.K. U K, etc.

You are then asked by your manager to create a report on all customers in Great Britain.

What do you do?

select * from tblCustomer where Country in ('GB', 'G.B.', 'Great Britain', 'Britain', 'UK', 'U.K.', 'U K')

What about: 'G B', 'United Kingdom'...... (the list is endless including all the spelling mistakes!!)

It is clear that having a foreign key onto a counties table would be better so that only 'Great Britain' can be entered rather than all the other combinations.

A simple example but, foreign keys are important to keep your database clean and not full of the rubbish that we as programmers are usually asked to process and produce reports from !!!

Hope this helps

Chris Dukes

 

[r937]

chris, very nice example

most apps would not make the user *enter* 'Great Britain' -- imagine the frustration if the user types something in and gets told "nope, that's wrong, try again"

rather, the chosen value of the foreign key is usually *selected* -- say, from a dropdown list

rudy

 

[cdukes]

Yes,

Clearly you would present the user with a drop down list.

Chris