Quick Question referential intergrity

Anon

Hi Guys,

I have created a couple of tables using MS Access 2000. I have a primary key in a the employee table called employee ID and the same field in the Additional_info table, this time as a foriegn key.

I have enabled referential integrity so that when i update the employee_id record in the employee table, it auto-updates the foriegn key in the additonal_info table.

Is this same feature available in Mysql?

Thanks!

Anon

No, because foreign keys aren't allowed.

Vincent, do you care to comment?

Anon

Foreign Key constraints are checked in InnoDB Tables in MySQL. However, MySQL plans on implementing Foreign Key constraints in Version 4.2.

I'm not sure what you mean by update. The spirit of Foreign Key constraints is that if it is violated the INSERT will fail.

Note: Foreign Key constraints are nice but hardly essential.

Marc

amcgrath

"Foreign Key constraints are nice..." if you want to maintain data integrity, that is 😉

smarlowe

QUOTE: Note: Foreign Key constraints are nice but hardly essential.

I would heartily disagree. I've seen so many poorly setup databases with extraneous and incorrect data and tables that don't match up right anymore.

If you want your data to stay in a consistent state, then they are essential. If you don't need consistent data, then why bother with using a dbms?

Anon

I think you guys missed the point. Yes, foreign key constraints are nice and can make up somewhat for mistakes made by people who don't know what they are doing but if you know you're way around a DBMS and employ proper programming skills, referential integrity will most likely never be a problem. Millions of people use MySQL and never have any difficulty.

Marc

Anon

Sorry, but the last place to put referential integrity is in the code on top of the database. Race conditions, failed partial inserts, and dozens of other issues can and do cause data corruption on a daily basis for people using such systems.

Sadly, I've seen such things implemented just as often on top of Oracle as on top of MySQL. I.e. people who refuse to use the database to ensure integrity, and thought they could code around the issue.

Invariably, over time, the data gets more and more screwed up, due to subtle, often very hard to chase down bugs.

A well designed database schema is the best place to start. Constraints scale much better than code on top of the database ever could, so that during a read of 400 users and an update by 20, there are no longer issues of your data reaching some incoherent state where you have orphan entries in sub tables due to race conditions et. al.

If you haven't seen this, keep programming and using databases, you will.

Anon

hey this has raised a great discussion which has opened my eyes completely and I thank everyone who has helped me out.

So, general consensus says: - no it cannot be done?

unlike MS Access 2000, there is no easy syntax in place that will force referential integrity???

wow, if this is the case then im going to stick with Access. Suppose I give this application to a novice after implementing in mysql, im going to have orphan data all over the place!

who's going to convince me to venture into the world of mysql?

Anon

Access is good for training, but has issues under load as well.

If you want to use Windows as your primary platform, it would be worth the money to get MSSQL server, or run NT/2K/XP and Postgresql under cygwin.

Postgresql under windows is a dog, but it's cheap and reliable. It's much faster under Unix or Linux so if you need performance you can always build a PII 300 to handle the database end quietly.

But MySQL is just not suited for tasks where you are worried about things like financial data, recovery from a power off, and things like that.

bfranco

I agree with Scott in every aspect. I think that PostgreSQL is great too, and I use it everyday.

But if you have to work under windows, you should try Interbase (firebird is the open source version).
It's free, it supports foreign keys, transactions, triggers, etc., and it's very easy to use. You can get it at:

http://firebird.sourceforge.net/download_Release100_RC2.php

Under unix it works fine, but I prefer PostgreSQL, as I said.

Anon

fantastic,

thanks for all the suggestions. Im going to research postgresql.

thanks again guys, what a great forum!

Anon

Thank Goodness for that, There I thought someone had misconstrued this whole thing for an endorsement of Access. eewwww.

While I will grant you the point. I don't think anyone believes MySQL to be a strong large scale solution to business problems. I guess because I use MySQL for it's wide spread acceptance as a multi-purpose, small scale web application database. No one here expects Fortune 500 corporations to start using it. However, if a person is using Microsoft Access they are hardly worried about problems arising from large scale, multi user environments. I am OCP Certified and would endorse Oracle to anybody, but even Oracle has issues. A guy ain't going to code his personal message book to interface with Oracle. I framed my response to what I figured the original poster's intentions were given they were using Access.

[deleted]

I'd love to comment, but Scot beat me to it.

so my comment is: everything scott said, plus:
Access is toy. Great for fooling around with database-ish things to learn the basics, but not at all suitable for any remotely serious application.

MySQL is simple, fast and furious, but lacks the R in RDBMS. The R is irriplacable. People like MYSQL because it lacks the R.
Many people don't understand or don't want to understand the R. That is, untill they see the sh*tload of code they have to write to try and replace the R. Then they start wondering why the database can't do that itself.... and they end up dishing MySQL and using a real RDBMS like PostgreSQL (free, fast and feature-rich), Informix (expensive, fast and feature-rich) and Oracle (expensive, fast, feature-rich and requires a four week training just to get it installed 🙂 ).

Anon

I tend to think of each in this way:

Access: Great data modelling tool, good for single user data sets.

MySQL: SQL interface to an ISAM db layer. Good for storing data that is mostly read, with no need for referential integrity (i.e. fit for content management, but not financial or CRM type data)

Interbase: Big step up from MySQL in dbms functionality (and runs on windows) and with very good referential integrity, but still better in a read mostly environment with few committers.

Postgresql: Open Source dbms grows up. With the latest versions we have everything here except clustering / failover. Fully implements SQL92 (except for two statements that no seems to even ask for or completely understand... :-) Very fast under heavy parallel load, even with many writers. Write ahead log design now ensures complete recoverability should power be lost during operation.

Oracle, Informix, SAPdb: What the big sites run. Often handling thousands of users at a time or more. Expensive but not as expensive as the data it often protects...

MSSQL: In the same league as interbase or Sybase, has many features, but isn't know for enterprise class scalability or reliability. When properly managed and placed on a big piece of intel iron though, it's quite capable of handling a few hundred users at a time reliably.

Anon

Hi!

Sorry, no. Referential integrity constraints are available in InnoDB type tables in MySQL, but they do not have the ON UPDATE CASCADE option.

If you anyway want to define a referential integrity constraint, and you want to change the referenced key of a parent row, you actually have to insert the updated parent row again with the updated key values, update the key in the children, and then delete the old parent.

Or use a dummy parent row:

1) to which you first move the children with an update statement;

2) then update the real parent;

3) then update the children again so that they get the updated key of the real parent.

You should put the steps above inside a single transaction so that they are rolled back if something goes wrong.

Best regards,

Heikki Tuuri

Innobase Oy

InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com