[NCLUG] mysql: backing up database - cannot save relationships

[Bob Proulx]

Hi Ben,

Ben West wrote:
> Phil, Bob, Et. Al. I watched this conversation helpless to interject as I
> wasn't sure how to close the gap on relationships (vs. Contraints, as they
> are called) but the overall conversation confused me in that I've never had
> a reason to use the database constraints feature, and I'm interested to
> know what factors make it a useful value-add.

First let me say that I haven't had any formal training in databases.
I have only used them a lot and I have read a lot of books on them.
Therefore I may be using terminology incorrectly or not in a canonical
way.  Beware.

The most common use of the word database today usually means a
relational database.  We often forget to say that "relational"
modifier but that is what we are talking about here.  Foreign keys are
what form the relationship in the relational database.

Say you have a database table with users.  You might have a table that
looks like this:

  CREATE TABLE users (
    id INTEGER PRIMARY KEY NOT NULL,
    name VARCHAR(255) DEFAULT NULL
  )

Basically a table with two fields.  The first field is the primary
key.  It will automatically increment as entries are created.  The
other field is the data.  Then lets use it in a relationship.

  CREATE TABLE admins (
    id INTEGER PRIMARY KEY NOT NULL,
    user_id INTEGER NOT NULL
  )

At this point we can create a user.

  INSERT INTO users (name) VALUES ('Bob');

And we can place that user into the admins table.  First we will need
to find the value that the db assigned as a key value to the user.
Then having gotten it we can assign it.

  SELECT id FROM users WHERE name = 'Bob';
  42

  INSERT INTO admins (user_id) VALUES (42);

And there we have created a foreign key relationship between an entry
in the admins table and an entry in the users table.

So now later if we want to get a list of admins we can query the
admins table for the user_id values.  And then we can query the users
table for who those users are using that value.

  SELECT user_id FROM admins;
  42

  SELECT name FROM users WHERE id = 42;
  Bob

Of course that is rather brute force.  Normally you would have the
database do both actions at once since doing this type of work with
data is the main purpose of databases.

  SELECT u.name FROM users u JOIN admins a ON u.id = a.user_id;
  Bob

All is good.  Now to delete this user you would do a number of steps.
Look up the user to get the id.  Delete the entry from the admins
table using it.  Delete the entry from the users table.

  SELECT id FROM users WHERE name = 'Bob';
  42
  DELETE FROM admins WHERE user_id = 42;
  DELETE FROM users WHERE id = 42;

All good!  No problems at all.  However some people are nervous about
making a mistake.  What if you make a coding error and delete things
in the wrong order.  All of the above above uses foreign keys.  But
none of the above says anything about foreign key constraints.  What
if you did things in this order?

  SELECT id FROM users WHERE name = 'Bob';
  42
  DELETE FROM users WHERE id = 42;
  ...network cable is unplugged right at this time...
  DELETE FROM admins WHERE user_id = 42;  <-- not done

Now you have an entry in the admins table with a user_id 42 but there
isn't a user 42 in the users database.  The data is no long
consistent.  An entry is left as a dangling pointer.  This is very
similar to having a C program and having malloc'd a chunk of memory
and then free'd it but then still hanging onto a pointer reference to
it.  If you try the previous lookups you get errors.  The database has
not enforced referential integrity.

  SELECT user_id FROM admins;
  ...empty...

  ...how can you even look it up?  If the code is bad it will try!
  SELECT name FROM users WHERE id = '';
  ...empty...
  ...What will your code do with this empty set?...

Or if you use the combined query:

  SELECT u.name FROM users u JOIN admins a ON u.id = a.user_id;
  ...empty...

What PHP will do depends upon the PHP library in use.  But a lot of
interpreted languages with the "whiny nil" behavior will trigger a
stack backtrace right at that point.  Burp!  Not good.

You can also get into this situation by inserting something in the
admins table that doesn't have a user behind it.

  INSERT INTO admins (user_id) VALUES (4242);

Therefore a lot of nervous people will advocate using database foreign
key constraints.  The user table is unchanged.  But the admins table
now could gain a constraint.

  CREATE TABLE admins (
    id INTEGER PRIMARY KEY NOT NULL,
    user_id INTEGER REFERENCES users;
  )

Or you could alter your table to add it.

  ALTER TABLE admins ADD CONSTRAINT FOREIGN KEY user_id REFERENCES users;

Now when you try to do things in the wrong way the database knows that
you are doing something bad and will throw a db error instead.

  SELECT id FROM users WHERE name = 'Bob';
  42
  DELETE FROM users WHERE id = 42;
  ...database error thrown...entry not deleted...

  SELECT id FROM users WHERE name = 'Bob';
  42

And this applies inserts too.

  INSERT INTO admins (user_id) VALUES (4242);
  ...database error thrown...entry not inserted...

Note that I didn't test any of the above.  I simply typed them in from
memory and a few double checks of syntax.  I could have made some
egregious errors of syntax in the above.  But hopefully it is good
enough to describe the situation regardless.  And if I have totally
gotten something fundamentally wrong then I would appreciate the
corrections.  But that is how I understand things.

I hope the above talk helps to make things more clear.

Also note that while database foreign keys have existed for as long as
relational databases have existed the concept of foreign key
constraints is relatively new in the grand scheme of things to the
popular dbs MySQL and SQLite.  MyIASM, the previous default MySQL
engine, doesn't support them at all.  InnoDB does but people have only
recently started flocking to InnoDB.  (Innodb is better than MyIASM.
Always use InnoDB.)  I think SQLite acquired the ability somewhere
near 2009.  I don't know about PostgreSQL.

> I get the impression that Phil is using PHP as his application, much

I think you meant s/PHP/phpmyadmin/ there.  PHP is simply the language
phpmyadmin happens to be written in.  But it could be written in any
language.

> in the same way one would use Microsoft Access, but am still

I am blissfully ignorant of MS Access.  Can I geta free(dom) copy that
will run on my Debian machine?
/me ducks and runs for cover.

> confused about the rationale.

Yes.  Me too.  I suppose it is because they want to make a web
interface to data and instead of using a web interface that is
specific to the application they are just using the generic phpmyadmin
mysql interface instead.

I think that is a trap.  Because it also prevents any safeguards
against accidents since you can do anything to the database from the
phpmyadmin interface.

But it is seductive since phpmyadmin is a quite pretty interface.  And
it is already available and so it suckers you into using it instead of
writing your own application specific web interface as you ought to do.

> I would love for more information about this to be a future NCLUG
> talk, and would go way out of my way to attend.

> Signed,
> Very Curious (Ben) in Loveland

Something like this might make a good topic for a future NCLUG talk.

Bob