Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tinytiny
Starting Member
3 Posts |
 Posted - 2013-10-10 : 16:39:25
|
| hi,My question is very basic but it's probably been asked 1000 times before.I have installed MySQL on my computer, set up the ERD to link my tables (giving me a simple Relational DB - Eg. Books - Loans - Customers)When i want to query the DB, why do i STILL have to 'test for equality' when i've already told MySQL where the links are?!! I've never thought to ask this question before and it bugs me now.NOTE: By Test for Equality, here is an example...WHERE Loan.CustomerID = Customer.CustomerIDAND Loan.BookID = Book.BookID.Surely i dont' need to tell the DB this after i've drawn my EER!thankstiny |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-10 : 16:48:40
|
| Because it can't assume how you want to link the tables in a query. There are times when you want to link to a different table instead. You must be very explicit in your queries. SQL makes no assumptions.What you are calling ERD Is referential integrity. It is not there to help you with your queries. It is there to protect the integrity of the database, the data specifically. It is there to protect itself from bad application code/stored procedures/adhoc modifications/etc. By the way, SQLTeam.com is for Microsoft SQL Server. Your question was generic enough that I could answer it, but future questions about MySQL should probably be asked on a MySQL site.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
tinytiny
Starting Member
3 Posts |
Posted - 2013-10-10 : 16:56:51
|
| Hi,thanks for your quick reply. MySQL issue noted.I would have thought that those relations, such as the ones i gave, would ALWAYS exist, no matter what. Based on my own very limited understanding, i thought the ERD defined the references/links which would never need to be broken. It made sense to me when someone asked me why we have to go to the bother of establishing those links again in an SQL statment.Regards, tiny |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-10 : 17:02:12
|
| They do always exist. They don't go away unless you drop them. They aren't there to help with queries, just to protect the data. This means that if you try to delete a row in the parent table but child data still exists, SQL will throw an error and will rollback the delete. That's the referential integrity at work, protecting the data. Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-10 : 17:03:05
|
| Besides, if you were to join the two tables without including the linking columns, then you would get a cartesian product. A cartesian product is the wanted result set at times, but usually you want only the linking data. SQL can't assume you don't or do want a cartesian product. You have to explicitly tell SQL what you want to return. No assumptions are made.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tinytiny
Starting Member
3 Posts |
Posted - 2013-10-10 : 17:06:37
|
| Hey, that's interesting... many thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-10 : 17:10:34
|
| You're welcome, glad to help.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Total Noob Q - but always wondered