I am using MySQL Work bench to model my database. Inside it, I have the choice to produce associations by determining foreign secrets, etc. What's the purpose of this? I can run my queries all right without these associations.
My tables will have a real column for foreign secrets, I simply did not define them in MySQL Work bench. Meaning, I do not have individuals lines hooking up the tables.
Foreign secrets are helpful for making certain data integrity. Should you define them, the database itself will make certain that the invalid foreign key value won't be permitted to become placed in to the table.
Foreign secrets perform a couple of things. Most clearly, you'll get built-in data integrity, as Macy Abbey pointed out.
Oddly enough, the query optimizer of the favorite RDBMS may also use constraints like foreign secrets to complete additional optimizations. Essentially, the database can determine whether it may perform a better job of writing a question than you. The existence of foreign secrets allows the database make these presumptions. You'll find an excellent example at Do Foreign Key Constraints Help Performance?. That can be a link is SQL Server specific, the key is identical no matter database engine.
If you don't have Foreign Secrets, you don't have Relations. Therefore you don't have a Relational database it's a bucket of seafood, without any integrity, and also you will not possess the energy of the Relational database (Jeremiah's answer is simply one item of numerous).
you are able to work without Foreign Secrets to model your database.. however, it is usually better to Foreign Secrets to keep data consistency.. If you're not using Foreign Secrets inside your database, you'll have to change your queries by hand and keep an eye on the foreign id's. however, should you choose specify the foreign keys' it's become simpler not just for you personally however for sql and to map your computer data..
Discover conscious of data integrity constraints and also the natural part they play in database design then I suggest you have a course or study some books prior to going about creating databases. It may be smart to hire some expertise to assist together with your current project.
Here are a few benefits you will get by using foreign key associations:
- Referential integrity (obviously)
- The optimizer can in some instances generate better execution plans
- Tool support (metadata, migration, analysis etc)
I personally use all sorts of constraint available, in the beginning. Then if it's proven to harm performance, I start getting rid of stuff. It's a good strategy which makes you are sleeping well during the night.