How do you implement referential integrity in MySQL?

[StageLeft]

I figured I may as well try and use this silly non-MS stuff on my skoorbs website, so I'm picking up php and mysql today 🙂

Anyway what's with all the table types and how can I set a foreign key for a field? I'm using cpanel on ripplehost (same one rossman set us up with a while ago) and the admin for mysql is php myadmin. I can't for the life of me find anywhere to set this foreign key relationship or tell how MySQL implements FKs... Thanks 🙂

 

[yllus]

MySQL Version 4.1.1-alpha Documentation: Foreign Keys

In MySQL Server 3.23.44 and up, InnoDB tables support checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See section 7.5.5.2 FOREIGN KEY Constraints.

For other table types, MySQL Server currently only parses the FOREIGN KEY syntax in CREATE TABLE commands, but does not use/store this info.

Cliff notes: Enforcing referential integrity in MySQL must currently be done manually.

 

[Aves]

wrong forum n00b!

 

[iwearnosox]

Originally posted by: aves2k
wrong forum n00b!

Not really, OT is where it's at for all the answers.

 

[cheesewhiz]

I thought the latest 4.1 alpha correctly implemented referential integrity.. then it's just like postgres.

but yeah, in all the stable versions, there's none at all. it's one of the reasons mysql can be wicked fast compared to other engines (with the expense of your time keeping everything sane). If you absolutely need it, try msql or postgressql.

 

[StageLeft]

Originally posted by: cheesewhiz
I thought the latest 4.1 alpha correctly implemented referential integrity.. then it's just like postgres.

but yeah, in all the stable versions, there's none at all. it's one of the reasons mysql can be wicked fast compared to other engines (with the expense of your time keeping everything sane). If you absolutely need it, try msql or postgressql.

Doesn't yullus's link indicate that .44 and up it supports it with innodb tables? ripplehost is using .49... I have no problem with just doing it myself and making sure not to insert/update/del the wrong records, but I wanted to do it right if I have to learn mysql 🙂

 

[PricklyPete]

Originally posted by: Skoorb

Originally posted by: cheesewhiz
I thought the latest 4.1 alpha correctly implemented referential integrity.. then it's just like postgres.

but yeah, in all the stable versions, there's none at all. it's one of the reasons mysql can be wicked fast compared to other engines (with the expense of your time keeping everything sane). If you absolutely need it, try msql or postgressql.

Doesn't yullus's link indicate that .44 and up it supports it with innodb tables? ripplehost is using .49... I have no problem with just doing it myself and making sure not to insert/update/del the wrong records, but I wanted to do it right if I have to learn mysql 🙂

Yes, that is the way I read the documentation...as long as both tables are of "type" InnoDB. Since I have exclusively used DB2 and MS SQL, I'm not very familiar with MySQL, but I had no idea it didn't implement FK Constraints...wow. I have been meaning to look into it for some non-work related projects, so this should be interesting after being so used to use full scale SQL dbs.

 

[StageLeft]

Yes, that is the way I read the documentation...as long as both tables are of "type" InnoDB. Since I have exclusively used DB2 and MS SQL, I'm not very familiar with MySQL, but I had no idea it didn't implement FK Constraints...wow. I have been meaning to look into it for some non-work related projects, so this should be interesting after being so used to use full scale SQL dbs.

Yeah I work with MS SQL all day and even Access has proper referential integrity built in. I don't even know what the heck a myisam or innodb table is in MySQL! :Q

 

[StageLeft]

So that's it hey, no referential integrity? So I'll just use the default table types and hop to it? 😛