Foreign key constraints...WHY??

Question

Foreign key constraints...WHY??

cm62597

SSC Eights!

Points: 976
More actions
March 31, 2010 at 11:51 am

#213345

We are creating a whole new database and I was wondering if it is a good idea to set foreign Key constraints on the tables?
Chad

Viewing 15 posts - 1 through 15 (of 70 total)

You must be logged in to reply to this topic. Login to reply

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

Foreign key constraint are to maintain the data integrity.

If your database is highly transacted, this integrity check may slow in performance. If you are managing the integrity properly while inserting or updating or deleting the records you can go ahead with out foreign key constraints. But the data integrity is the concern here.

Lowell SSC Guru Points: 323518 More actions · Answer 2

I'd also note that Referential Integrity is a best practice; be sure to read all the articles on Best Practices /Worst Practices here on SSC to get a good flavor for stuff like this.

I've personally seen a lawsuit occur to get out of a contract because a vendor provided a database without any constraints, but their contract boasted they follow industry best practices. They apparently thought that their schema would expose company secrets or something, and that foreign keys would help people reverse engineer their data and application.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

david_wendelken SSCrazy Points: 2388 More actions · Answer 3

Yes, you definitely should.

But...

I'm not saying this to be mean, so please don't take it this way.

If you're asking "why should I do this?", you probably don't have the skills to pick the foreign keys CORRECTLY. Or, more accurately, design the database correctly.

(Because otherwise you would know why...)

My advice is to find someone who is a good data modeler and have them do a design review BEFORE you invest more effort in coding the application.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

David has my vote on this one.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Yes there needs to be foreign keys in the database. Furthermore, there needs to be indexes built on those keys.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

david_wendelken SSCrazy Points: 2388 More actions · Answer 6

Plus primary and unique key constraints, check constraints, non-unique indexes, default values, column and table definitions, and maybe even triggers. Plus database roles and privileges, clearly defined up front.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

None of these things are to be taken lightly. Each must be considered.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

My recommendation would be to first lookup "Constraints" and the "classes" under that heading in Books Online. That's a great place to start learning about all of the different types of constraints and why you should use them. It's not a difficult read.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

And be sure to pick the appropriate index on each table to be the clustered index. It doesn't have to be the primary index. Look at how the tables will be accessed to help determine the clustered index.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

See how this really isn't just a simple question?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 11

in short, hire a senior DBA with proven experience in data modeling and physical implementation 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

PaulB-TheOneAndOnly (4/2/2010)
in short, hire a senior DBA with proven experience in data modeling and physical implementation 🙂

Superb Idea

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sumit.joshij Ten Centuries Points: 1040 More actions · Answer 13

In every tranasaction, a database maintain the statistics.

SO when we create the relation between the PK and FK it check the statistics for PK and FK. it can take time for a huge tranasaction database because it check all the time for its PK and FK relation and after the Every DML command it again resuffual the database table tree.

SO it is avaided in maintaining the PK and FK relation. instead of you can use it in the Interface level handling.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

sumit.joshij (4/2/2010)
In every tranasaction, a database maintain the statistics.
SO when we create the relation between the PK and FK it check the statistics for PK and FK. it can take time for a huge tranasaction database because it check all the time for its PK and FK relation and after the Every DML command it again resuffual the database table tree.
SO it is avaided in maintaining the PK and FK relation. instead of you can use it in the Interface level handling.

So you are saying NOT to use foreign key constraints?