MySQL – Adding Multiple Foreign Keys to Single Table

foreign keyinnodbMySQLtable

I'm trying to add multiple foreign keys to the same table; however, the error code 1215: Cannot add foreign key constraint keeps popping up. I've read other people's posts on this same topic and tried to add innodb, but the error is still popping up. Any help is appreciated!

Here's my code:

Create Database if not exists Research;
Use Research;

Create table if not exists CompanyInfo
(
CID int primary key, Company varchar(45), 
SIC int, InnovativeIndex int
) 
engine = innodb;

Create table if not exists TimePeriod
(
Year int primary key, GDP int, GDPgrowth int, 
inflation decimal(5,2), unemployment decimal(5,2)
) 
engine = innodb;

Create Table If not exists CompanyRank 
(
CID int, Year int, IndexYN int, SPRank int, FortuneRank int, 
primary key (CID, Year), index (CID), Index(year),
foreign key (CID) references CompanyInfo.CID,
foreign key (Year) references TimePeriod.Year
) 
engine = innodb;

Best Answer

You have a syntax problem, try this :

Create Table If not exists CompanyRank
(
CID int, Year int, IndexYN int, SPRank int, FortuneRank int,
primary key (CID, Year), index (CID), Index(year),
foreign key (CID) references CompanyInfo (CID),
foreign key (Year) references TimePeriod (Year)
) engine = innodb;

the reference is "CompanyInfo (CID)", not "CompanyInfo.CID"

Related Solutions

Sql-server – Allow self-referential foreign keys to be null from an alter-table

If there are no illegal values in C.ParentID, then I am not clear what the problem might be. Is this Microsoft SQL Server?

I created a test case as follows:

Create Table dbo.Hier
(Id  INT IDENTITY Primary KEy,
 ParID INT NULL,
 Other varchar(10));
GO
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Able');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (1, 'Baker');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Charlie');
GO
ALTER TABLE dbo.Hier ADD 
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
GO
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
GO
DROP TABLE dbo.Hier;
GO

Seems to work fine. If you are on a different SQL Server it might have different behavior. Or fluentMigrator may have a problem.

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Best Answer

Related Solutions

Sql-server – Allow self-referential foreign keys to be null from an alter-table

Mysql – Adding index to large thesql tables

Related Question