Foriegn Keys

fatoaf

Am i being stupid or is there no way to set up foriegn keys in MYSQL? Maybe its just a limitation of mysqlfront which i am using. Surely it must be possible to do this somehow?

Cheers,

Oaf

Elena_Mitovska

Foreign keys are realy not implemented in current MYSQL versions but promised to be added in next ones. The only way to sustain refferential integrity is only by programming methods.
By the way MySQL also doesn't support transactions.

barand

Herein lies an extract from my MySql bible

7.5.4.2 Foreign Key Constraints
InnoDB version 3.23.44 features foreign key constraints. InnoDB is the first MySQL table
type which allows you to define foreign key constraints to guard the integrity of your data.
An example:
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INNODB;
Both tables have to be InnoDB type and there must be an index where the foreign key
and the referenced key are listed as the first columns.

fatoaf

so can you create InnoDB tables in most new versions of MYSQL?

barand

I'm using MySQL 3.23.47 and it let me create the tables in the example quoted in the manual (prev post). I had never tried it before. Looking at the extract from the manual, its looks as though 3.23.44 and above have it.

However, having created the example tables and creating parents 1,3,4 it let me add add a child with parentid=2 🙁

More work needed on this.

Sxooter

If you're gonna play with things like this take a look at postgresql. It has support for all the fancy stuff like foreign keys with cascading deletes, deferrable constraints, stored procedures (in like 5 or so languages) user defineable types, locale support, multibyte character support, fully transactional, etc... etc...

MySQL is a pretty good backend data store for things like content management, but for real database work it has a hard time matching postgresql's features, and contrary to popular (mis)belief, postgresql is quite fast nowadays.