How do I set up Foreign Key references from db.xml?
Permalink
I have several tables which reference each others' IDs.
I want those set up using foreign keys, so if I delete a row from a certain table without changing other tables' rows referencing that one, I'll get a error.
I want that done from db.xml, since that's how it's ought to be, isn't it?
So, how do I set up foreign key references from db.xml?
I want those set up using foreign keys, so if I delete a row from a certain table without changing other tables' rows referencing that one, I'll get a error.
I want that done from db.xml, since that's how it's ought to be, isn't it?
So, how do I set up foreign key references from db.xml?
I have not used constraints with db.xml but the file format is documented for C5 here:
http://www.concrete5.org/documentation/how-tos/creating-and-working...
and more general information about the AXMLS format can be found here:
http://phplens.com/lens/adodb/docs-datadict.htm#xmlschema...
http://www.concrete5.org/documentation/how-tos/creating-and-working...
and more general information about the AXMLS format can be found here:
http://phplens.com/lens/adodb/docs-datadict.htm#xmlschema...
I solved my problem.
Here's a little tutorial if you're searching for the same thing (since there is little about it in the docs):
1. Check if your db engine is InnoDB (mysql default prior to version 5.5 is MyISAM).
Constratints only work with InnoDB
2. In your db.xml check that every field you want to be a Foreign Key has all the same attributes as it's reference field (if reference field is unsigned integer, your field should be too).
3. Add <constraints> tag to your field in db.xml
It should look like this:
Note the comma.
That's it.
Here's a little tutorial if you're searching for the same thing (since there is little about it in the docs):
1. Check if your db engine is InnoDB (mysql default prior to version 5.5 is MyISAM).
Constratints only work with InnoDB
2. In your db.xml check that every field you want to be a Foreign Key has all the same attributes as it's reference field (if reference field is unsigned integer, your field should be too).
3. Add <constraints> tag to your field in db.xml
It should look like this:
<field name="user_id" type="I"> <unsigned /> <constraint> , foreign key (user_id) references Users(uID) </constraint> </field>
Note the comma.
That's it.
Thanks, lunatik! After almost 4 years, your post is still useful. And Concrete5's documentation still sucks...
But does anybody knows the proper syntax?
I would be thankful for any example.