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>

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

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

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

    <sql>
        <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>
    </sql>
</schema>

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.