More Than 1 Foreign Key In A Table?

[peterlaursen]

“QLyog reports an error when I try and do the other one”

Please always tell what error!  copy the dialogue if you want and attach here.

But maybe it is so simple that there must be an index on both the referencing as the referenced column.  Try creating a key on PicID here:

Code:

CREATE TABLE `orderitems` (
`ItemID` int(10) unsigned NOT NULL auto_increment,
`OrderID` int(11) unsigned NOT NULL default '0',
`PicID` int(11) unsigned NOT NULL default '0',
`Quantity` int(11) NOT NULL default '0',
`Material` varchar(20) NOT NULL default '',
`Area` int(11) NOT NULL default '0',
PRIMARY KEY (`ItemID`),
KEY `FK_orderitems` (`OrderID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

.

[antipotato]

Whatever I do, it seems to result in this error:

“Error No. 1005

Can't create table '.parkerpix#sql-420_1.frm' (errno: 150)”

EDIT: ok, worked something out, but I'm not sure I did it the right way round.

For a one-to-many relationship, to create the foreign key in the 'many' table, do you do the create relationship while that table is selected?

[peterlaursen]

“For a one-to-many relationship, to create the foreign key in the 'many' table, do you do the create relationship while that table is selected?”

Do do not create ” one-to-many relationship” while creating the Foreing Key.  Whether it is one-to-one or one-to many is decided by the type of the index on the referenced column

To create a Foreign key both the referencing and the referenced column must have an index.  

Where you create the index as a primary key or a unique index there will only one – if the key is not Primary or unique it will be many.

The most common way is to create a PK from a (not-unique)indexed column on the 'child' to the Primary Key column of the 'parent' . An example: the 'orders' table has a column named 'customer'.  Create a non-unique index on that 'customer' and a Foreign key pointing to the (PK) 'id' column of the 'customers' table.  Every customer can only have one entry in the 'customers' tablke but more in the 'orders' table (because he may place more than one order).

[Author]

Posts