More Than 1 Foreign Key In A Table?

[antipotato]

Hi, I'm relatively new to SQL, and making my first proper database with it. I've done ones in Access before, and a few example SQL ones. I'm having a bit of trouble creating foreign keys though. My normalised E-R diagram is

Customers –< Orders -< OrderItems >– Pictures

(The non-normal form one is

Customers –< Orders >< Pictures) I've managed to create a foreign key in Orders for CustomerID. That was fine. Then with OrderItems, I need 2 foreign keys, right? OrderID and PicID. But after I create one foreign key, SQLyog reports an error when I try and do the other one. So either I'm doing it wrong or I don't need foreign keys. Help! What am I doing wrong? If it makes any difference, I'm using PHP as a frontend. Thanks! EDIT: looking around, it seems you like the sql… customers CREATE TABLE `customers` ( `CustomerID` int(11) unsigned NOT NULL auto_increment, `Title` varchar(10) NOT NULL default '', `FirstName` varchar(40) NOT NULL default '', `LastName` varchar(40) NOT NULL default '', `AddressLine1` varchar(40) NOT NULL default '', `AddressLine2` varchar(40) default NULL, `AddressLine3` varchar(40) default NULL, `Postcode` varchar(8) NOT NULL default '', `PhoneNumber` varchar(11) NOT NULL default '', `EmailAddress` varchar(50) NOT NULL default '', `Password` varchar(40) NOT NULL default '', `Super` tinyint(1) NOT NULL default '0', `Plants` char(3) NOT NULL default '0', `Landscapes` char(3) NOT NULL default '0', PRIMARY KEY (`CustomerID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 orderitems 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 orders CREATE TABLE `orders` ( `OrderID` int(11) unsigned NOT NULL auto_increment, `CustomerID` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`OrderID`), KEY `FK_orders` (`CustomerID`), CONSTRAINT `FK_orders` FOREIGN KEY (`CustomerID`) REFERENCES `customers` (`CustomerID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 pics CREATE TABLE `pics` ( `PicID` int(11) unsigned NOT NULL auto_increment, `PicName` varchar(50) NOT NULL default '', `Details` varchar(200) default NULL, `PicAddr` varchar(100) NOT NULL default '', `Category` int(2) default NULL, `DateAdded` date NOT NULL default '0000-00-00', PRIMARY KEY (`PicID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

[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