Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-08T07:56:55.183Z
Answers.LastActivityDate: 2016-06-08T07:56:55.183Z
Answers.UserDisplayName: Steve Holland
Body: <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>
  
Tags: <plugin-development><database>
Title: Add Database Relationship to craft_user table from a Plugin
ThreadText: # 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:
  
  ```
  <?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.
  
  **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.
  
  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`.
  
  
  ---
  
  