Foreign Keys just get in the way

apapadimoulis

From Bryce K. Nielsen ...

I've worked with the "big" databases (i.e. Oracle and MS SQL Server) and recently started a project that used MySql. Being my first foray into the OpenSource database, I asked a couple technical questions to the "top" consultant (who had 10 yrs+ under his belt):

Consultant: MySql is just as good as the "big" databases. You should be able to use the same syntax in your Create script as you do in other Databases.

Me: Great. What about Foreign Keys, what's the syntax for creating them?

Consultant: Foreign Keys?

Me: Yeah, to keep Referencial Integrity in the database.

Consultant: Oh, MySql doesn't do Foreign Keys, we maintain those in our PHP scripts. Besides, in my experience, Foreign Keys tend to just get in the way.

Besides his lack of knowledge on innodb, I was blown away by his statement that "Foreign Keys just get in the way". He always backed it up by saying thousands of websites, including Fortune500 companies, use MySql and so it must be good technology, as though blaming the technology for his lack of understanding core principles.

ammoQ

Foreign keys are bad. They expose bugs the customer might (with some luck) never notice. ;-)

zip

I've always wondered why I couldn't figure out how to use MySQL foreign keys, and I always felt dirty for not figuring out how to do it.

Now that I know they don't exist.... nope, still dirty.

DZ_Jay

@zip said:

I've always wondered why I couldn't figure out how to use MySQL foreign keys, and I always felt dirty for not figuring out how to do it.

Now that I know they don't exist.... nope, still dirty.

Actually, Foreign Keys have been implemented in MySQL for a while They only work in InnoDB databases, though, which incidentally are the only ones which support transactions. The default MyISAM database type is mostly designed for fast performance of data retrieval, not as a full featured system.

RTFM? Yup, we've heard of it.

-dZ.

ferrengi

@ammoQ said:

Foreign keys are bad. They expose bugs the customer might (with some luck) never notice. ;-)

Well, you can get around that if you conveniently leave out any code in your application that actually looks for database errors.

ferrengi

Yeah, foreign keys do get in the way... of the user trying to do something stupid in the application.
I once had to deal with a conversion into my company's database from a huge piece of garbage database. They must have about 2000 tables in there and nobody knew the database structure.
The data was complete garbage. We had negative values in time fields. The structure was so loose that it was impossible to tell what tables to link to try to get the data we need from there. Every table had a key which was a pointer to one or more tables. Sometimes the parent record existed and sometimes it didn't. I guess it depended on the mood of the program on the particular day or maybe something was only partially deleted by the program, who knows?
Some of the tables didn't even have primary keys and so there were duplicate records and we didn't know which one to convert into our system.

Point is, if the db had been designed properly this vendor probably would not have lost the client and the client would still be able to use it and actually run some meaningful reports.

negative_one

PostgreSQL

Open source. Faster and more robust than Oracle. Used for big and little databases. ACID compliant, allows stored procedures and user-defined datatypes. Everything that MySQL isn't.

This is database nirvana.

...and what's up with this forum software? WTF?

lluthor

@negative one said:

... Faster and more robust than Oracle. ...

Oh come on!

Oracle has a LOT of problems and many WTFs in their own right, BUT speed is not among them (at least not compared to PG).

For example, on Oracle there is no difference between an empty string and NULL.

felix

@lluthor said:

on Oracle there is no difference between an empty string and NULL.

And that's a good thing?

ammoQ

@felix said:

@lluthor said:
on Oracle there is no difference between an empty string and NULL.

And that's a good thing?

Yes, it's even excellent as long as you get paid per hour. Make a fortune in searching bugs like

if length('')<1 then
  /* never executed... */
  do_something_important;
end if;

lluthor

@felix said:

And that's a good thing?

I meant that to point out one of the legitimate WTFs in Oracle, rather than its performance.

PG despite all its flaws strives very hard to do things the right way, rather than introducing strange new WTFs like that.

felix

@lluthor said:

I meant that to point out one of the legitimate WTFs in Oracle, rather than its performance.

Sorry, I was reading in a hurry yesterday.

felix

@hank miller said:

Between sqlite (www.sqlite.org) on the low
end, and postgresql on the high end, I see no need for mysql
anywhere.

Indeed? What if you need concurrent access to the database? SQLite
can't handle it. Not by itself. Are you willing to code the concurrency
layer youself when there's a ready-made alternative?

And what if the hosting provider doesn't... well... provide PostgreSQL?
Our customers seldom have their own server. How about yours?

@hank miller said:

Anytime someone is using mysql (even with the
newer backend that supports useful database concepts like ACID), you
can be sure you will find a few WTFs, the convoluted almost sql syntax
ensures that.

If you mean the famous multiple-table delete, that's meant precisely to
compensate for the lack of both foreign keys and transactions in MyISAM
tables. Which, of course, is a WTF in itself, but it's not the
programmer's fault. Not always, anyway.

El_Foo

@felix said:

@hank miller said:
Between sqlite (www.sqlite.org) on the low
end, and postgresql on the high end, I see no need for mysql
anywhere.

Indeed? What if you need concurrent access to the database? SQLite
can't handle it. Not by itself. Are you willing to code the concurrency
layer youself when there's a ready-made alternative?

If you need concurrent access in situations where SQLite will break, use Postgre. For small applications, a few locks never hurt anyone, and for larger applications SQLite is unsuitable anyway.

jwenting

@El Foo said:

@felix said:
@hank miller said:
Between sqlite (www.sqlite.org) on the low end, and postgresql on the high end, I see no need for mysql anywhere.

Indeed? What if you need concurrent access to the database? SQLite can't handle it. Not by itself. Are you willing to code the concurrency layer youself when there's a ready-made alternative?

If you need concurrent access in situations where SQLite will break, use Postgre. For small applications, a few locks never hurt anyone, and for larger applications SQLite is unsuitable anyway.

Ah, the idea that small applications will forever stay small...

Forget it, applications ALWAYS grow out of whatever their scale was intended to be. If it was intended to be a pilot for another application that would be using a "proper" database, be especially scared.

You need only 1 database engine that's both fast and smalle: Firebird.
Though I prefer to code in Java using database independent code so switching to something big like DB/2 or Oracle isn't usually a problem...

ammoQ

@jwenting said:

Forget it, applications ALWAYS grow out of
whatever their scale was intended to be. If it was intended to be a
pilot for another application that would be using a "proper" database,
be especially scared.

"ALWAYS" is not true. Some applications are dumped soon after going
live (especially the WTF ones, but there can be other reasons). Some
applications run for 10 years or more, mostly unchanged, with the same
number of users as in the beginning. If you see a charcater-based user
interface somewhere, it's probably one of them. But many applications
grow faster than expected, insofar I agree. To be on the safe side,
prepare for growth.