CODEWITHSUNDEEP
mysqlforeign-keysforeign-key-relationship
Saranya Jothiprakasam
Saranya Jothiprakasam

Reputation: 294

How to create a table with composite foreign key

I created the master table with the composite primary key.

parent table structure is as follows:

CREATE TABLE `taskcategory` (
  `SiteID` int(10) unsigned NOT NULL DEFAULT 1,
  `TaskID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TaskName` varchar(45) DEFAULT '',
  `TaskDescription` varchar(45) DEFAULT '',
  `IsInbuild` int(11) DEFAULT '1',
  PRIMARY KEY (`TaskID`,`SiteID`)
);

when i am trying to create the table with foreign key with the above parent table reference i am getting 'can't create table error no 150' error . help me to do that.

child table structure as follows:

CREATE TABLE taskdetails (`SiteID` int(10) unsigned NOT NULL DEFAULT '1',
  `TaskID` int(10) unsigned NOT NULL DEFAULT '0',
  `SubtaskID` int(10) unsigned NOT NULL,
  `ScriptName` varchar(255) DEFAULT '',
  `FunctionName` varchar(255) DEFAULT '',
  `ButtonName` varchar(255) DEFAULT '',
  `IsInbuild` int(10) unsigned DEFAULT '1',
  `Description` varchar(255) DEFAULT '',
  PRIMARY KEY (`SubtaskID`,`TaskID`,`SiteID`),
                            INDEX (siteid, taskid),
                            FOREIGN KEY (siteid, taskid)
                              REFERENCES taskcategory(siteid, taskid)
                              ON UPDATE CASCADE ON DELETE CASCADE
                            ) ENGINE=INNODB;

help me to resolve it.

Upvotes: 0

Views: 454

Answers (2)

fancyPants
fancyPants

Reputation: 51888

From the manual:

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

So when you add an index in the parent table it works (yes, I tested it):

CREATE TABLE `taskcategory` (
  `SiteID` int(10) unsigned NOT NULL DEFAULT 1,
  `TaskID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TaskName` varchar(45) DEFAULT '',
  `TaskDescription` varchar(45) DEFAULT '',
  `IsInbuild` int(11) DEFAULT '1',
  PRIMARY KEY (`TaskID`,`SiteID`)
  , INDEX (SiteID, TaskID)
) ENGINE=INNODB;

CREATE TABLE taskdetails (`SiteID` int(10) unsigned NOT NULL DEFAULT '1',
  `TaskID` int(10) unsigned NOT NULL DEFAULT '0',
  `SubtaskID` int(10) unsigned NOT NULL,
  `ScriptName` varchar(255) DEFAULT '',
  `FunctionName` varchar(255) DEFAULT '',
  `ButtonName` varchar(255) DEFAULT '',
  `IsInbuild` int(10) unsigned DEFAULT '1',
  `Description` varchar(255) DEFAULT '',
  PRIMARY KEY (`SubtaskID`,`TaskID`,`SiteID`)
                            ,INDEX (SiteID, TaskID)
                            ,FOREIGN KEY (SiteID, TaskID)
                              REFERENCES taskcategory(SiteID, TaskID)
                              ON UPDATE CASCADE ON DELETE CASCADE
                            ) ENGINE=INNODB;

You have a primary key on those columns already (which means there's an implicit index), but the order of the columns is important!

Upvotes: 1

Alex Monthy
Alex Monthy

Reputation: 1877

Your table definition of taskcategory lacks the ENGINE=InnoDB clause, and probably this is not your system's default. Foreign key relations can only be set up between InnoDB tables.

Upvotes: 0

Related Questions