SQL Query for Table with Multiple Foreign Keys?

Question

I know that MySQL supports having multiple foreign keys in a table. Running into an issue with this table creation that is resulting in the error: Error Code: 1215. Cannot add foreign key constraint

CREATE TABLE IF NOT EXISTS ActivelyCore.Locations (
  idLocation INTEGER NOT NULL AUTO_INCREMENT,
    CONSTRAINT idLocation_Uniq UNIQUE (idLocation),
    CONSTRAINT idLocation_Pk PRIMARY KEY (idLocation),
  idOrganization INTEGER NOT NULL,
    INDEX (idOrganization),
    CONSTRAINT Locations_Fk_idOrganization FOREIGN KEY (idOrganization)
      REFERENCES ActivelyCore.Organizations (idOrganization),
  idAddress INTEGER NOT NULL,
    INDEX (idAddress),
    CONSTRAINT Locations_Fk_idAddress FOREIGN KEY (idAddress)
      REFERENCES ActivelyCore.Address (idAddress),
  name           VARCHAR(250) NOT NULL,
  telephone      VARCHAR(15),
  email          VARCHAR(200),
  website        VARCHAR(200),
  hours          VARCHAR(1000),
  tags           VARCHAR(1000)
);

Any ideas on what might be wrong in the query?

http://stackoverflow.com/questions/17691282/error-code-1215-cannot-add-foreign-key-constraint-foreign-keys — blur0224, Jun 17 '16 at 18:55
Firstly, it's very bad practice to add constraints and indexes at the column definition level as it's very hard to read. Secondly, the tables that the constraints reference must already exist. So, if you are creating multiple tables in alphabetic order, the ActivelyCore.Organizations table does not yet exist. Good practice is to create all the tables and then add constraints at the end of the script for this very reason. The exception to this is adding a primary key. — T Gray, Jun 17 '16 at 18:55

score 0 · Accepted Answer · answered Jun 17 '16 at 18:58

First create referenced tables with their respectives Primary key then you can create Locations table.

CREATE TABLE ActivelyCore.Organizations (
idOrganization INTEGER PRIMARY KEY
...)
CREATE TABLE ActivelyCore.Adress(
idAdress INTEGER PRIMARY KEY
...)

then

CREATE TABLE IF NOT EXISTS ActivelyCore.Locations (
      idLocation INTEGER NOT NULL AUTO_INCREMENT,
      idAddress INTEGER NOT NULL,
      idOrganization INTEGER NOT NULL,
      name           VARCHAR(250) NOT NULL
      telephone      VARCHAR(15),
      email          VARCHAR(200),
      website        VARCHAR(200),
      hours          VARCHAR(1000),
      tags           VARCHAR(1000),
        CONSTRAINT idLocation_Pk PRIMARY KEY (idLocation),
        INDEX (idOrganization),
        CONSTRAINT Locations_Fk_idOrganization FOREIGN KEY (idOrganization)
          REFERENCES ActivelyCore.Organizations (idOrganization),
        INDEX (idAddress),
        CONSTRAINT Locations_Fk_idAddress FOREIGN KEY (idAddress)
          REFERENCES ActivelyCore.Address (idAddress)
    );

SQL Query for Table with Multiple Foreign Keys?

1 Answers1