Referential Integrity

teammatt3 posted this at 00:27 — 12th April 2007.

Joined: Sep 2003

I'm trying to set referential integrity on two tables in my database. I already created them, so I think I would use the ALTER TABLE command.

This is the code I'm using

ALTER TABLE tblSchedule
(
FOREIGN KEY (CID) REFERENCES tblCourses (CID) 
)

When I run it I get this error

Quote:
SQL query:

ALTER TABLE tblSchedule(
FOREIGN KEY ( CID ) REFERENCES tblCourses( CID )
)

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(
FOREIGN KEY (CID) REFERENCES tblCourses (CID)
)' at line 2

I'm a total noob to mysql, I have absolutely no idea if that is the right syntax or if it is the right idea to run it like that.

EDIT: I guess I don't really need it for this project but it would be nice to know how to do it in the future. You know of any good resources on ri in mysql?

bhammer posted this at 04:06 — 12th April 2007.

They have: 10 posts

Joined: Apr 2007

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

ChadR posted this at 16:55 — 12th April 2007.

They have: 43 posts

Joined: Mar 2007

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}]

The Best User Driven Poker Site, where you can find Poker Games in your area and get the latest Poker Strategy.

andy206uk posted this at 17:28 — 12th April 2007.

He has: 1,758 posts

Joined: Jul 2002

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?

Andy

teammatt3 posted this at 17:47 — 12th April 2007.

He has: 2,102 posts

Joined: Sep 2003

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.

JeevesBond posted this at 20:13 — 12th April 2007.

He has: 3,956 posts

Joined: Jun 2002

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.

a Padded Cell our articles site!