Hi folks
Does Propel allow optional foreign keys?
The documentation hints that this is true but it's not clear to me.
Consider a 'child' record which might have a 'parent' record but can exist
as an orphan when necessary.
To implement this I added: required="false" to the column definition. The
documentation suggested this here:
and a LEFT JOIN for foreign keys attached to a non-required column
So I have a table with a non-required foreign key.
<table name="ptbook" description="Identifies a booking">
<column name="id" type="integer" required="true" primaryKey="true"
autoIncrement="true"/>
<column name="staffid" type="integer" size="11" description="FK to user"
required="false"/>
<foreign-key foreignTable="user" onDelete="CASCADE">
<reference local="staffid" foreign="id"/>
</foreign-key>
...
</table>
When I generate the SQL it looks normal:
CREATE TABLE `ptbook`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`staffid` INTEGER(11) COMMENT 'FK to user',
...
PRIMARY KEY (`id`),
INDEX `ptbook_FI_2` (`staffid`),
CONSTRAINT `ptbook_FK_2`
FOREIGN KEY (`staffid`)
REFERENCES `user` (`id`)
ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Identifies a booking';
However when I try inserting a booking without a staffid I get the error
message:
> [04-Jan-2012 17:01:13] PHP Fatal error: Uncaught exception
> 'PropelException' with message 'Unable to execute INSERT statement [INSERT
> INTO `ptbook` (`ID`,`SLOTID`,`STAFFID`,`STUDENTID`,`SEATS`) VALUES
> (:p1,:p2,:p3,:p4,:p5)] [wrapped: SQLSTATE[23000]: Integrity constraint
> violation: 1452 Cannot add or update a child row: a foreign key constraint
> fails (`focus_sobs`.`ptbook`, CONSTRAINT `ptbook_FK_2` FOREIGN KEY
> (`staffid`) REFERENCES `user` (`id`) ON DELETE CASCADE)]' in
> /home/murray/dev/sobs2/build/propel/util/BasePeer.php:293
Propel version 1.5.6-dev
Please let me know if this is possible, otherwise what is the best option
for achieving this.
PS I have searched through the group archive and found similar messages,
but they were different problems.
Cheers
Murray
