forums › forums › SQLyog › Using SQLyog › More Than 1 Foreign Key In A Table?
- This topic is empty.
-
AuthorPosts
-
-
February 27, 2007 at 4:50 am #23460
peterlaursen
Participant“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.
-
February 28, 2007 at 9:21 am #23461
antipotato
MemberWhatever 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?
-
February 28, 2007 at 10:48 am #23462
peterlaursen
Participant“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).
-
-
AuthorPosts
- You must be logged in to reply to this topic.