Database Design: what's the point of identifying foreign keys?

I'm using MySQL Workbench to model my database. In it, I have the option to create relationships by identifying foreign keys, etc. What's the point of this? I am able to run my queries just fine without these relationships.

My tables do have an actual column for foreign keys, I just didn't define them in MySQL Workbench. Meaning, I don't have those lines connecting the tables.

-------------Problems Reply------------

Foreign keys do a few things. Most obviously, they provide you with built in data integrity, as Macy Abbey mentioned.

Interestingly, the query optimizer of your favorite RDBMS can also use constraints like foreign keys to do additional optimizations. Basically, the database can determine if it can do a better job of writing a query than you. The presence of foreign keys lets the database make these assumptions. You can find a great example at Do Foreign Key Constraints Help Performance?. While that link is SQL Server specific, the principle is the same regardless of database engine.

Foreign keys are useful for ensuring data integrity. If you define them, the database itself will make sure that an invalid foreign key value will not be allowed to be inserted into the table.

If you do not have Foreign Keys, you do not have Relations. Therefore you do not have a Relational database; it is a bucket of fish, with no integrity, and you won't have the power of a Relational database (Jeremiah's answer is just one item of many).

Here are some benefits you gain from using foreign key relationships:

  1. Referential integrity (of course)
  2. The optimizer can in some cases generate better execution plans
  3. Tool support (metadata, migration, analysis etc)

I use every type of constraint available, at first. Then if it is shown to hurt performance, I start removing stuff. It is a good strategy that makes you sleep well at night.

you can work without Foreign Keys to model your database.. however, it is always advisable to Foreign Keys to maintain data consistency.. If you are not using Foreign Keys in your database, you will have to alter your queries manually and keep track of the foreign id's. however, if you do specify the foreign keys' it's become easier not only for you but for sql also to map your data..

If you aren't aware of data integrity constraints and the important role they play in database design then I recommend you take a course or study some books before you go about designing databases. It might be a good idea to hire some expertise to help with your current project.

Category:mysql Views:0 Time:2010-11-24

Related post

Copyright (C), All Rights Reserved.

processed in 0.189 (s). 11 q(s)