JeevesBond — Thu, 12 Apr 2007 20:13:28 +0000

Or even if you have PostgreSQL available you can do foreign key constraints, just a thought.

It may well be erroring because your table is of type MyISAM instead of InnoDB.

matt wrote: I think it is really useful, especially when you have clients working directly with the data, and you don't want them to screw stuff up.

It is pretty useful in Access, MySQL is a totally different product however. A user generally has no idea that MySQL is even behind the interface they're using, let alone having full access to the fiddle with the tables! :shock:

Generally I believe the policy is that the programmer will sort out stuff like foreign key constraints in the business rules (the C bit in MVC) of their application. Saying that it would be quite handy to have better support for this sort of thing, on the other hand I would never condone allowing users to fiddle with the underlying tables in anything more than an small Access database.

teammatt3 — Thu, 12 Apr 2007 17:47:46 +0000

It forces you to enter in valid data. So it's harder to make mistakes. If I had two tables that I wanted to create a relationship with, the primary key (ID) of tblApple is the foreign key on tblOrange. MySQL would not allow me to enter in a bad foreign key in tblOrange; an ID that didn't exist in tblApple. I use it a lot in MS Access. It also allows you to do a cascade delete so if you wanted to delete everything related to the ID 5, you could delete all the information on it in one shot. I think it is really useful, especially when you have clients working directly with the data, and you don't want them to screw stuff up.

andy206uk — Thu, 12 Apr 2007 17:28:52 +0000

Lol... I've been using MySQL for a few years now and never come across this. Although I use primary/foreign keys to join tables and whatnot I didn't realise you could actually do this. What benefits does this method have to not doing it?

ChadR — Thu, 12 Apr 2007 16:55:48 +0000

Are You using InnoDB or MyiSAM?

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

InnoDB allows you to add a new foreign key constraint to a table by using ALTER TABLE:

ALTER TABLE tbl_name
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

bhammer — Thu, 12 Apr 2007 04:06:14 +0000

well i remember using one of those free mysql manager to get the job done. It too darn troublesome to remember all those alter codes