Adding Foreign Keys in db.xml in concrete5

Published on October 26, 2012 by

Shamefully, a lot of concrete5 developers do not make use of foreign keys in their database to help ensure data integrity. concrete5 uses XML to define a block or package’s database structure. More precisely, a file named db.xml is parsed. This can be leveraged to add foreign key constraints.

Initially, you might think that you can just add a constraint element nested under a field element. However, this does not seem to be possible, and even if it were, it probably takes some creativity with SQL to accomplish. What can be done instead is to have an sql element after the table definitions which contains one or more query elements. In these elements, you may write any SQL query.

To add a foreign key constraint, we can simply do an ALTER TABLE query after the tables have been created and add it. First, create an index for the column you want to reference another column. Notice that the name of the index – and also the foreign key – must be unique across the entire database.

In the following example, we add a foreign key from the ce_user table to the ce_usergroup table (i.e. a user is member of a usergroup).

<?xml version="1.0"?>
<schema version="0.3">
    <table name="ce_user">
        <field name="user_id" type="I">
            <key />
            <autoincrement />
            <unsigned />

        <field name="username" type="C" size="50">
                , UNIQUE (username)
	<field name="usergroup_id" type="I" size="10">
            <unsigned />

    <table name="ce_usergroup">
        <field name="usergroup_id" type="I">
            <key />
            <autoincrement />
            <unsigned />

        <field name="name" type="C" size="50"></field>

        <query>CREATE INDEX idx_ce_user_usergroup_id ON ce_user (usergroup_id)</query>
        <query>ALTER TABLE ce_user ADD CONSTRAINT fk_ce_user_usergroup_id FOREIGN KEY (usergroup_id) REFERENCES ce_usergroup (usergroup_id)</query>

Note that the columns should be of the same type. Also, most (if not all) database engines automatically create an index if a column is set to be unique. Therefore, creating an index implicitly is not needed in order to create a foreign key constraint on unique columns.

This is just one way of doing this. An alternative is to make a normal database call in the package or block controller’s install method, like this:

public function install() {
	try {
		$db = Loader::db();
		$db->Execute('CREATE INDEX idx_ce_user_usergroup_id ON ce_user (usergroup_id)');
		$db->Execute('ALTER TABLE ce_user ADD CONSTRAINT fk_ce_user_usergroup_id FOREIGN KEY (usergroup_id) REFERENCES ce_usergroup (usergroup_id)');
	} catch (Exception $e) {
		throw new Exception(t('An error occurred while creating foreign keys.'));

However, most people would probably agree that it is better to keep this in the db.xml file so that the definition of the database schema is not scattered throughout a package or block.

If you know of a better way to add foreign key constraints, please share your knowledge in a comment.

Author avatar
Bo Andersen

About the Author

I am a back-end web developer with a passion for open source technologies. I have been a PHP developer for many years, and also have experience with Java and Spring Framework. I currently work full time as a lead developer. Apart from that, I also spend time on making online courses, so be sure to check those out!

2 comments on »Adding Foreign Keys in db.xml in concrete5«

  1. jeff milton

    just an FYI for all you readers who might come across this. Concrete 5 (at least 5.7), ignores the tag :(

    • Thank you for your comment, Jeff. This approach was tested with version of concrete5. I am sorry to hear that it does not seem to work anymore.

Leave a Reply

Your e-mail address will not be published.