#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0

    My foreign keys are giving me problems


    Hello everyone! I was looking for a strong programming forum and I came across this site.

    I'm looking for a little assistance with some MySQL code that I have. I'm getting Error 1005 at 150 and from the research that I have done it seems that it is centered around my Foreign Keys. I can't figure out what I've done wrong. Could someone take a look and let me know please:

    Code:
        CREATE TABLE IF NOT EXISTS `#__si_customers` (
                `cust_id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
                `cust_name` VARCHAR(40),
                `cust_email` VARCHAR(80),
                `cust_contact` VARCHAR(250),
                PRIMARY KEY (`cust_id`),
                KEY `idx_name` (`cust_name`)
        ) TYPE=InnoDB;
         
         
         
         
        CREATE TABLE IF NOT EXISTS `#__si_invoices` (
          `inv_id`      MEDIUMINT(4) UNSIGNED NOT NULL AUTO_INCREMENT ZEROFILL,
          `cust_num`    MEDIUMINT(4) ZEROFILL,
          `total`               DECIMAL(10, 2),
          `inv_date`    DATE,
          `due_date`    DATE,
          `category`    TINYINT UNSIGNED,       -- Not used at this time.  For future versions.
          `comments`    TEXT,
          `user`                TINYINT,                        -- Not sure what data type is required for Joomla user information.
          PRIMARY KEY (`inv_id`),
          FOREIGN KEY (`cust_num`) REFERENCES #__si_customers(`cust_id`)
                ON UPDATE CASCADE
                ON DELETE CASCADE,
          KEY `idx_inv_date` (`inv_date`),
          KEY `idx_due_date` (`due_date`),
          KEY `idx_total` (`total`)
        ) TYPE=InnoDB;
         
         
         
         
        CREATE TABLE IF NOT EXISTS `#__si_items` (
                `item_id`       SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
                `inv_num`       SMALLINT(4) UNSIGNED,
                `item`          VARCHAR(30),
                `desc`          VARCHAR(80),
                `qty`           TINYINT(4) UNSIGNED,
                `unit_val`      DECIMAL (10, 2),
                `tax_id`        SMALLINT(4) UNSIGNED,   --  what is the id for the type of tax that will be applied to this item?
                PRIMARY KEY (`item_id`),
                FOREIGN KEY (`inv_num`) REFERENCES #__si_invoices(`inv_id`)
                        ON UPDATE CASCADE
                        ON DELETE CASCADE
        ) TYPE=InnoDB;
         
         
         
         
        CREATE TABLE IF NOT EXISTS `#__si_taxes` (
                `tax_id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
                `tax_name` VARCHAR(15) NOT NULL,
                `tax_rate` DECIMAL(6, 5),
                PRIMARY KEY (`tax_name`)
        ) TYPE=InnoDB;
         
         
         
         
        CREATE TABLE IF NOT EXISTS `#__si_categories` (
                `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
                `category_name` VARCHAR(15),
                PRIMARY KEY (`category_name`)
        ) TYPE=InnoDB;
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    you need backticks `` around #__si_invoices in the foreign key declaration, because it's not a standard SQL identifier.

    Did you choose those odd table names?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0
    Thank you for the quick response. That was one issue but the other issue was that my foreign keys were not declared as the same data type as the field that it references.

    I do have one other question:

    Once it successfully ran, the collation of the tables that I created was swedish_ci. How do I set them to utf_general_ci?
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by jim.thornton
    Once it successfully ran, the collation of the tables that I created was swedish_ci. How do I set them to utf_general_ci?
    http://dev.mysql.com/doc/refman/5.5/...set-table.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0
    Thanks.

IMN logo majestic logo threadwatch logo seochat tools logo