Actively Actively - 5 months ago 7
SQL Question

SQL Query for Table with Multiple Foreign Keys?

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?

Answer

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)
    );