Login | Register
My pages Projects Community openCollabNet

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

propel
Discussion topic

Back to topic list

Re: [propel] Many-to-Many Relationship Problem Fix

Reply

Author hlellelid
Full name Hans Lellelid
Date 2004-04-12 12:34:14 PDT
Message Hi Matt--

Thanks for the code sample. This is almost 100% right. The only thing we
need to check is whether the col was already specified (in the schama) as
being indexed. I'll do this now & should have it ready for you to test in
a few minutes.

Thanks-
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
>
>


--------------------​--------------------​--------------------​---------
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 | 11 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: