tutorialreferences

Tutorial :: Creating references between tables

In our example of a member site, we might want to add mailing list functionality and allow members to subscribe or unsubscribe.

Let's begin by creating a new migration file 003_mailing_lists.cfc and create a simple table to store mailing list names and descriptions.

Here is some code for this, just like the way we created the members table in our first migration:

<cfcomponent extends="plugins.dbmigrate.Migration" hint="adds mailing lists and member subscriptions">
  <cffunction name="up">
    <cfscript>
    t = createTable('mailinglists');
    t.string('name');
    t.text('description');
    t.timestamps();
    t.create();
    </cfscript>
  </cffunction>
  <cffunction name="down">
    <cfscript>
    dropTable('mailinglists');
    </cfscript>
  </cffunction>
</cfcomponent>

Next we'll create a join table to store member subscriptions. This table will need to reference both the members and mailinglists tables. As this is related to the mailing lists, it makes sense to put it in the same migration (You don't need to create a new migration for every table).

This new table, we'll call subscriptions doesn't necessarily need a primary key. So we'll begin creating a new Table Definition object without a primary key:

t = createTable(name='subscriptions',id=false); Next we'll use the references function to create columns memberid and mailinglistid. By default (unlike Rails Active Record) foreign key constraints will also be setup to members(id) and mailinglists(id). Note that the reference name is the singular of the table name.

t.references('member,mailinglist'); Then we'll add a column to record when the member subscribed to the mailing list called createdat which will automatically be set by CFWheels. Then call the create function to execute the table definition creation.

t.timestamp('createdat'); t.create(); Don't forget to drop this table too. Because of the foreign key constraints you need to drop this table before you drop the mailinglists table. The DBMigrate plugin will also detect the foreign key constraints and remove them before dropping the table.

dropTable('subscriptions'); So our final migration file looks like this:

t = createTable('mailinglists'); t.string('name'); t.text('description'); t.timestamps(); t.create();

t = createTable(name='subscriptions',id=false);
t.references('member,mailinglist');
t.timestamp('createdat');
t.create();
</cfscript>

dropTable('subscriptions');

dropTable('mailinglists');
</cfscript>

Save your file and load up your application in a browser. Click on the DBMigrate link under Plugins in the CFWheels framework debugging footer, or if you already have it open, refresh the page. You should see that this new migration shows up in the available migrations.

Use the select box to choose this migration and click the go button. This will migrate the database to version 003 and provide feedback to say that it added the new tables and foreign keys.

Check your database to confirm.

Now try migrating the database back to version 002 and check the database again to confirm the tables have been removed. It is always good to check as you go that your down migrations are working.

Before you continue re-run the migration to return to version 003.

Next we'll look at adding database indexes.