Add Database Relationship to craft_user table from a Plugin

Question

Within a plugin I'd like to create a table which is linked to the craft_users table, keyed on the id. In my table I have a user_id column which I'd like to be unique and only contain Id's which exist in the craft_users table. Now, I know I can't monkey about with the craft_users table or the UserRecord, so I wondered what the best way of doing this would be?

Here is my Record code:

<?php
namespace Craft;

class MyPlugin_MerchantRecord extends BaseRecord
{
  public function getTableName()
  {
    return 'myplugin_merchants';
  }

  protected function defineAttributes()
  {
    return [
      'user_id' => [AttributeType::Number, 'required' => true],
      ...
    ];
  }

  public function defineIndexes()
  {
      return [
          ['columns' => ['user_id'], 'unique' => true],
      ];
  }

  public function defineRelations()
  {
    return [
      'user' => [static::BELONGS_TO, 'UserRecord', 'user_id'],
    ];
  }
}

If I try and install the plugin I get the following error:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint. 
The SQL statement executed was: ALTER TABLE `craft_myplugin_merchants`    
ADD CONSTRAINT `craft_myplugin_merchant_user_id_fk` FOREIGN KEY (`user_id`) 
REFERENCES `craft_users` (`id`) ON DELETE SET NULL

I've tried it with/without the defineIndexes() call, and get the same error. I'm sure I'm missing something simple, but can't see the wood for the trees!

As always any help gratefully received.

Answer 1

So it looks like I had a couple of things wrong here.

1. The type of relationship I was trying to use.
2. The fact that I was trying to add an extra unique index.

To solve the problem, what I ended up doing was this:

• Remove my user_id column.
• Remove the defineIndexes() definition altogether (you'll see why in a minute).

• Change my Relationship definition to this:

  public function defineRelations()
  {
    return [
      'user' => [static::BELONGS_TO, 'UserRecord',  'id', 'required' => true, 
                      'onDelete' => static::CASCADE],
    ];
  }
  

So what this ended up doing was using the id column that is automatically created as both a Primary Key and a Foreign Key related to the craft_user table.

The reason I wanted to do this, is my new table which I'm creating is actually just an extension of the craft_user table anyway. There will be only ever one row per User (which is made unique by the Primary Key index) and the Foreign Key makes sure that only values that exist in the craft_users id column are allowed in the id column of my new table.

For completeness, the reason I was getting the error mentioned in the question was because I'd set the user_id column to 'required' => true. As I'd not used the onDelete property on the relationship, it was defaulting to ON DELETE SET NULL. It can't be both, either it must always have a value or it can be set to NULL.