Login | Register
My pages Projects Community openCollabNet

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

propel
Discussion topic

Back to topic list

[propel] Many-to-Many Relationship Problem

Reply

Author matthewh
Full name Matthew Hershberger
Date 2004-04-12 10:30:16 PDT
Message 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
Attachments

« Previous message in topic | 5 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: