Answers.Body: <p>Ok, so I figured it out. </p>
  
  <p>The reason it fails to create the tables is because the foreign key has to be of the same type as the primary key it's relating to. </p>
  
  <p>So I just changed:</p>
  
  <pre><code>'quoteId' =&gt; AttributeType::String,
  </code></pre>
  
  <p>to:</p>
  
  <pre><code>'quoteId' =&gt; AttributeType::Number,
  </code></pre>
  
  <p>And it worked!</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-07-28T13:32:02.413Z
Answers.LastActivityDate: 2015-07-28T13:32:02.413Z
Answers.UserDisplayName: Mark Gifford
Body: <p>I'm trying to create some relational tables from 2 records but I keep getting the mysql error: </p>
  
  <p>CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1005 Can't create table 'craft1.#sql-75c_603' (errno: 150)</p>
  
  <p>The relationship is between 2 tables: quote and quotedItem. I based the code off the relationship seen here: <a href="http://www.yiiframework.com/doc/guide/1.1/en/database.arr" rel="nofollow">http://www.yiiframework.com/doc/guide/1.1/en/database.arr</a> between the Post and User tables.</p>
  
  <p>The tables create fine without the relationship defined (I've also noticed they create with the relationship but I also get the above mysql error when installing the plugin so I presume it hasn't worked correctly).</p>
  
  <p>I'm new to making plugins so there's probably something obvious I'm doing wrong but if someone could point me in the right direction that would be great.</p>
  
  <p>Here's my code for quoted Item: </p>
  
  <pre><code>class ShoppingCart_QuotedItemRecord extends BaseRecord
  {
      public function getTableName()
      {
          return 'shoppingcart_quoteditems';
      }
  
      protected function defineAttributes()
      {
          return array(
              'quoteId' =&gt; AttributeType::String,
              'itemId' =&gt; AttributeType::String,
              'quantity' =&gt; AttributeType::String
          );
      }
  
      public function defineRelations()
      {
          return array(
              'quote' =&gt; array(static::BELONGS_TO, 'ShoppingCart_QuoteRecord', 'quoteId'),
          );
      }
  }
  </code></pre>
  
  <p>And here's quote:</p>
  
  <pre><code>class ShoppingCart_QuoteRecord extends BaseRecord
  {
      public function getTableName()
      {
          return 'shoppingcart_quotes';
      }
  
      protected function defineAttributes()
      {
          return array(
              'title' =&gt; AttributeType::String,
              'country' =&gt; AttributeType::String,
              'name' =&gt; AttributeType::String,
              'email' =&gt; AttributeType::String,
              'company' =&gt; AttributeType::String,
              'phone' =&gt; AttributeType::String,
              'department' =&gt; AttributeType::String,
              'reference' =&gt; AttributeType::String,
              'questions' =&gt; AttributeType::String
          );
      }
  
      public function defineRelations()
      {
          return array(
              'quotedItems' =&gt; array(static::HAS_MANY, 'ShoppingCart_QuotedItemsRecord', 'quoteId'),
          );
      }
  }
  </code></pre>
  
Tags: <plugin-development><plugins><relations><database><records>
Title: Mysql error when defining related tables
ThreadText: # Mysql error when defining related tables
  
  (Asked by: username_0 on 2015-07-28)
  
  I'm trying to create some relational tables from 2 records but I keep getting the mysql error:
  
  CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1005 Can't create table 'craft1.#sql-75c\_603' (errno: 150)
  
  The relationship is between 2 tables: quote and quotedItem. I based the code off the relationship seen here: <http://www.yiiframework.com/doc/guide/1.1/en/database.arr> between the Post and User tables.
  
  The tables create fine without the relationship defined (I've also noticed they create with the relationship but I also get the above mysql error when installing the plugin so I presume it hasn't worked correctly).
  
  I'm new to making plugins so there's probably something obvious I'm doing wrong but if someone could point me in the right direction that would be great.
  
  Here's my code for quoted Item:
  
  ```
  class ShoppingCart_QuotedItemRecord extends BaseRecord
  {
      public function getTableName()
      {
          return 'shoppingcart_quoteditems';
      }
  
      protected function defineAttributes()
      {
          return array(
              'quoteId' => AttributeType::String,
              'itemId' => AttributeType::String,
              'quantity' => AttributeType::String
          );
      }
  
      public function defineRelations()
      {
          return array(
              'quote' => array(static::BELONGS_TO, 'ShoppingCart_QuoteRecord', 'quoteId'),
          );
      }
  }
  
  ```
  
  And here's quote:
  
  ```
  class ShoppingCart_QuoteRecord extends BaseRecord
  {
      public function getTableName()
      {
          return 'shoppingcart_quotes';
      }
  
      protected function defineAttributes()
      {
          return array(
              'title' => AttributeType::String,
              'country' => AttributeType::String,
              'name' => AttributeType::String,
              'email' => AttributeType::String,
              'company' => AttributeType::String,
              'phone' => AttributeType::String,
              'department' => AttributeType::String,
              'reference' => AttributeType::String,
              'questions' => AttributeType::String
          );
      }
  
      public function defineRelations()
      {
          return array(
              'quotedItems' => array(static::HAS_MANY, 'ShoppingCart_QuotedItemsRecord', 'quoteId'),
          );
      }
  }
  
  ```
  
  **Tags:** plugin-development, plugins, relations, database, records
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2015-07-28. Score: 1
  
  Ok, so I figured it out.
  
  The reason it fails to create the tables is because the foreign key has to be of the same type as the primary key it's relating to.
  
  So I just changed:
  
  ```
  'quoteId' => AttributeType::String,
  
  ```
  
  to:
  
  ```
  'quoteId' => AttributeType::Number,
  
  ```
  
  And it worked!
  
  
  ---
  
  