<p>So it looks like I had a couple of things wrong here.</p>

<ol>
<li>The type of relationship I was trying to use. </li>
<li>The fact that I was trying to add an extra unique index.</li>
</ol>

<p>To solve the problem, what I ended up doing was this:</p>

<ul>
<li>Remove my <code>user_id</code> column.</li>
<li>Remove the <code>defineIndexes()</code> definition altogether (you'll see why in a minute).</li>
<li><p>Change my Relationship definition to this:</p>

<pre><code>public function defineRelations()
{
  return [
    'user' =&gt; [static::BELONGS_TO, 'UserRecord',  'id', 'required' =&gt; true, 
                    'onDelete' =&gt; static::CASCADE],
  ];
}
</code></pre></li>
</ul>

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

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

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

CC BY-SA 3.0
2016-06-08T07:56:55.183Z
2016-06-08T07:56:55.183Z
Steve Holland
<p>Within a plugin I'd like to create a table which is linked to the <code>craft_users</code> table, keyed on the <code>id</code>. In my table I have a <code>user_id</code> column which I'd like to be unique and only contain Id's which exist in the <code>craft_users</code> table. Now, I know I can't monkey about with the <code>craft_users</code> table or the <code>UserRecord</code>, so I wondered what the best way of doing this would be? </p>

<p>Here is my Record code:</p>

<pre><code>&lt;?php
namespace Craft;

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

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

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

  public function defineRelations()
  {
    return [
      'user' =&gt; [static::BELONGS_TO, 'UserRecord', 'user_id'],
    ];
  }
}
</code></pre>

<p>If I try and install the plugin I get the following error:</p>

<pre><code>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
</code></pre>

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

<p>As always any help gratefully received.</p>

<plugin-development><database>
Add Database Relationship to craft_user table from a Plugin
# Add Database Relationship to craft_user table from a Plugin

(Asked by: username_0 on 2016-06-07)

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:
 [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.

**Tags:** plugin-development, database


---

## 1 answers

### Answer by username_0 on 2016-06-08. Score: 2 | Accepted answer

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

The type of relationship I was trying to use. 
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.


---

