Login | Register
My pages Projects Community openCollabNet

Discussions > users > [propel] Many-to-Many Relationship Problem Fix

propel
Discussion topic

Back to topic list

[propel] Many-to-Many Relationship Problem Fix

Reply

Author matthewh
Full name Matthew Hershberger
Date 2004-04-12 12:31:19 PDT
Message Hans,

I did this to propel/templates/sql​/base/mysql/foreignk​ey.tpl to make it
work:

<?php foreach ($table->getForeignKeys() as $fk) { ?>
     INDEX (<?php echo $fk->getLocalCol​umnNames()?>),
     FOREIGN KEY (<?php echo $fk->getLocalCol​umnNames()?>) REFERENCES
<?php echo $fk->getForeignTableName() ?> (<?php echo
$fk->getForeignColumnNames() ?>),
<?php } ?>

Notice the added INDEX line. I don't know if that will work in every
case but at least create-db now works again.

  I didn't commit this to CVS because I don't understand foreign keys
well yet.

Hope that helps : )

- Matt

On Apr 12, 2004, at 15:20, Matthew B. Hershberger wrote:

> Hans,
>
> Your description exactly describes my problem. The manual and examples
> say it should work but I wanted to make sure I wasn't doing something
> wrong.
>
> Thanks for your help,
>
> Matt
>
> On Apr 12, 2004, at 14:53, Hans Lellelid wrote:
>
>> Hi Matt,
>>
>> Yes, I believe this is a known issue (and in the bug tracker for me to
>> tackle). I believe the error you got had something to do with lack of
>> indexes on the fkey constraints, correct? This seems to be a
>> mysql/innodb
>> issue -- well, not an issue, just a difference between mysql and
>> other dbs
>> that use fkeys. This should be no problem to fix, since we have
>> individual templates for all the db systems. I just haven't done it
>> yet
>> :) I think there's even some code on the dev list.... I'll try to get
>> this done ASAP.
>>
>> If I'm wrong about the problem, let me know.
>>
>> Thanks,
>> Hans
>>
>>> Hello,
>>>
>>> I have never used Foreign Keys before and on Saturday I found a
>>> problem. I'm not sure if there is a problem in propel or maybe I
>>> missed
>>> something in my schema file. The schema is attached below.
>>>
>>> I created the first query after seeing a working example in the mysql
>>> manual.
>>>
>>> The following CREATE TABLE works:
>>> CREATE TABLE account_priv_ref(
>>>
>>> account INTEGER NOT NULL ,
>>>
>>> privilege INTEGER NOT NULL ,
>>>
>>> PRIMARY KEY(account,privilege),
>>> INDEX(account),
>>> FOREIGN KEY (account) REFERENCES account (id),
>>> INDEX(privilege),
>>> FOREIGN KEY (privilege) REFERENCES privilege (id)) Type=InnoDB;
>>>
>>> This query was created by the propel build file.
>>> This CREATE TABLE fails: (errno: 150 [foreign-key problem])
>>> CREATE TABLE account_priv_ref(
>>>
>>> account INTEGER NOT NULL ,
>>>
>>> privilege INTEGER NOT NULL ,
>>>
>>> PRIMARY KEY(account,privilege),
>>> FOREIGN KEY (account) REFERENCES account (id),
>>> FOREIGN KEY (privilege) REFERENCES privilege (id)) Type=InnoDB;
>>>
>>> Here are the other two related tables:
>>> CREATE TABLE account(
>>>
>>> id INTEGER NOT NULL ,
>>>
>>> firstName VARCHAR(30) NOT NULL ,
>>>
>>> lastName VARCHAR(30) NOT NULL ,
>>>
>>> email VARCHAR(100) ,
>>>
>>> username VARCHAR(30) NOT NULL ,
>>>
>>> password VARCHAR(30) NOT NULL ,
>>>
>>> externalId VARCHAR(30) ,
>>>
>>> PRIMARY KEY(id)) Type=InnoDB;
>>>
>>> CREATE TABLE privilege(
>>>
>>> id INTEGER NOT NULL ,
>>>
>>> name VARCHAR(50) NOT NULL ,
>>>
>>> namepsace VARCHAR(200) NOT NULL ,
>>>
>>> PRIMARY KEY(id)) Type=InnoDB;
>>>
>>> Mysql version is 4.1.0-max-alpha, binary distribution, on freebsd
>>> 4.9.
>>>
>>> Schema
>>> ---
>>> <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
>>> <!DOCTYPE database SYSTEM "../dtd/database.dtd">
>>>
>>> <database name="athena" defaultIdMethod="native">
>>> <table name="account" description="User Table">
>>> <column name="id" required="true" primaryKey="true" type="INTEGER"
>>> description="id" />
>>> <column name="firstName" required="true" type="VARCHAR" size="30"
>>> description="First Name of User" />
>>> <column name="lastName" required="true" type="VARCHAR" size="30"
>>> description="Last Name of User" />
>>> <column name="email" required="false" type="VARCHAR" size="100"
>>> description="Email Address of User" />
>>> <column name="username" required="true" type="VARCHAR" size="30"
>>> description="Account username" />
>>> <column name="password" required="true" type="VARCHAR" size="30"
>>> description="Account password" />
>>> <column name="externalId" required="false" type="VARCHAR" size="30"
>>> description="External Account Id" />
>>> </table>
>>>
>>> <table name="privilege" description="List of all Privileges">
>>> <column name="id" required="true" primaryKey="true" type="INTEGER"
>>> description="id" />
>>> <column name="name" required="true" type="VARCHAR" size="50"
>>> description="Name of Privilege" />
>>> <column name="namepsace" required="true" type="VARCHAR" size="200"
>>> description="Namespace the privilege resides in" />
>>> </table>
>>>
>>> <table name="account_priv_ref" description="Maps Accounts to
>>> Available
>>> Privileges" />
>>> <column name="account" type="INTEGER" required="true"
>>> primaryKey="true"
>>> />
>>> <column name="privilege" type="INTEGER" required="true"
>>> primaryKey="true" />
>>> <foreign-key foreignTable="account">
>>> <reference local="account" foreign="id"/>
>>> </foreign-key>
>>> <foreign-key foreignTable="privilege">
>>> <reference local="privilege" foreign="id"/>
>>> </foreign-key>
>>> </table>
>>>
>>> </database>
>>>
>>> Thanks,
>>>
>>> Matt
>>
>>
>> --------------------​--------------------​--------------------​---------
>> To unsubscribe, e-mail: users-unsubscribe@pr​opel.tigris.org
>> For additional commands, e-mail: users-help at propel dot tigris dot org
>>
>>
>
>
> --------------------​--------------------​--------------------​---------
> To unsubscribe, e-mail: users-unsubscribe@pr​opel.tigris.org
> For additional commands, e-mail: users-help at propel dot tigris dot org
>
>


--------------------​--------------------​--------------------​---------
To unsubscribe, e-mail: users-unsubscribe@pr​opel.tigris.org
For additional commands, e-mail: users-help at propel dot tigris dot org

« Previous message in topic | 10 of 15 | Next message in topic »

Messages

Show all messages in topic

[propel] Syntax error in the manual. matthewh Matthew Hershberger 2004-04-09 07:10:13 PDT
     Re: [propel] Syntax error in the manual. hlellelid Hans Lellelid 2004-04-09 07:20:30 PDT
         Re: [propel] Syntax error in the manual. matthewh Matthew Hershberger 2004-04-09 09:46:16 PDT
             Re: [propel] Syntax error in the manual. hlellelid Hans Lellelid 2004-04-09 12:05:36 PDT
                 [propel] Many-to-Many Relationship Problem matthewh Matthew Hershberger 2004-04-12 10:30:16 PDT
                     Re: [propel] Many-to-Many Relationship Problem hlellelid Hans Lellelid 2004-04-12 11:53:52 PDT
                         Re: [propel] Many-to-Many Relationship Problem matthewh Matthew Hershberger 2004-04-12 12:20:37 PDT
                             RE: [propel] Many-to-Many Relationship Problem "Denny H dot Shimkoski" <dhs at hamfish dot org> "Denny H dot Shimkoski" <dhs at hamfish dot org> 2004-04-12 12:15:32 PDT
                                 RE: [propel] Many-to-Many Relationship Problem hlellelid Hans Lellelid 2004-04-12 12:31:33 PDT
                             [propel] Many-to-Many Relationship Problem Fix matthewh Matthew Hershberger 2004-04-12 12:31:19 PDT
                                 Re: [propel] Many-to-Many Relationship Problem Fix hlellelid Hans Lellelid 2004-04-12 12:34:14 PDT
                                     RE: [propel] Many-to-Many Relationship Problem "Denny H dot Shimkoski" <dhs at hamfish dot org> "Denny H dot Shimkoski" <dhs at hamfish dot org> 2004-04-12 12:27:38 PDT
                                 [propel] Fix applied -- Re: [propel] Many-to-Many Relationship Problem Fix hlellelid Hans Lellelid 2004-04-12 12:42:38 PDT
                                     [propel] Duplicate primaryKey in column does not through error until insert-sql matthewh Matthew Hershberger 2004-04-12 13:16:02 PDT
                                         Re: [propel] Duplicate primaryKey in column does not through error until insert-sql hlellelid Hans Lellelid 2004-04-12 13:25:27 PDT
Messages per page: