You have to put your ForeignKeyConstraint in the __table_args__ for the
PhoneNumber class - see
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#table-configuration
for details. Something like:
class PhoneNumber(db.Model):
__tablename__ = 'phonenumbers'
phone_number = db.Column(db.Numeric(precision=4, scale=0),
primary_key=True)
exchange_exchange = db.Column(db.Integer, primary_key=True)
exchange_area_code_pk = db.Column(db.Integer, primary_key=True)
__table_args__ = (db.ForeignKeyConstraint(
['exchange_exchange', 'exchange_area_code_pk'],
['exchange.exchange', 'exchange.area_code_pk'],
),
)
Simon
On 7 Mar 2013, at 23:49, Randall Degges <[email protected]> wrote:
> Hi Lloyd,
>
> Thank you! I believe this is what I was trying to figure out, although I am
> having further issues now. Here's a recent pastie with my improved models,
> along with the errors I'm now having, http://pastie.org/6417080
>
> What I've done (as you can probably see) is I've used the
> ForeignKeyConstraint on my PhoneNumber table, to ensure that I'm able to link
> to my exchanges table properly. Unfortunately, this looks like it is not
> being picked up by SQLAlchemy, since it's complaining that I have no Foreign
> Keys for that table.
>
> Also, just for reference, I've totally wiped / recreated my DBs to test this,
> so I'm sure it isn't a result of migrations or anything like that.
>
> If I run \d phonenumbers inside of psql, I get the following output
> (http://pastie.org/6417088), thought that might also be of help.
>
> Thank you all for your help so far, I can't wait to get this working!
>
> Best,
>
> -Randall
>
>
> On Thu, Mar 7, 2013 at 5:41 AM, Lloyd Kvam <[email protected]> wrote:
> While primary_key is specified twice, once for each column, there is only ONE
> primary key which is a composite.
>
> You need to use ForeignKeyConstraint at the Table level to specify a
> composite foreign key.
>
> You need to provide two lists, the local table columns, and the corresponding
> foreign table columns.
> Now you know what to search for if you need more information.
>
> from the schema definition language docs
> It’s important to note that the ForeignKeyConstraint is the only way to
> define a composite foreign key. While we could also have placed individual
> ForeignKey objects on both theinvoice_item.invoice_id and
> invoice_item.ref_num columns, SQLAlchemy would not be aware that these two
> values should be paired together - it would be two individual foreign key
> constraints instead of a single composite foreign key referencing two columns.
>
>
> On Wednesday, March 6, 2013 9:00:56 PM UTC-5, Randall Degges wrote:
> Hi Mike,
>
> Sorry about that, I actually had a typo there. I've got a correct code sample
> available here
> http://stackoverflow.com/questions/15260842/how-can-i-build-a-foreignkey-to-a-table-which-has-multiple-primary-keys
> (just posted it).
>
> Thank you,
>
> -Randall
>
>
> On Wed, Mar 6, 2013 at 5:54 PM, Michael Bayer <[email protected]> wrote:
> a database table can only have one primary key (hence "primary"), but that
> key can contain more than one column (a "composite" primary key).
>
> the model you have here is a little unclear, did you mean for the primary key
> of Exchange to be "exchange" , and the primary key of PhoneNumber to be the
> composite of "exchange" and "phone number" ? that would be my guess as to
> what you're looking for.
>
>
>
> On Mar 6, 2013, at 6:05 PM, Randall Degges <[email protected]> wrote:
>
>> Hi all,
>>
>> I'm having a lot of trouble figuring out how to properly build my ForeignKey
>> column for a table I'm defining. I've outlined my models here:
>> http://pastie.org/6407419# (and put a comment next to the problematic line
>> in my PhoneNumber model).
>>
>> Here's what's happening:
>>
>> My Exchange table has two primary keys. This is required for my use case.
>>
>> The PhoneNumber table I'm trying to define needs a ForeignKey to the
>> Exchange table, but since the Exchange table has two primary keys, I can't
>> figure out how to make the relationship work.
>>
>> Any guidance would be appreciated. Thank you.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>>
>> To post to this group, send email to [email protected].
>>
>> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
>
> To post to this group, send email to [email protected].
>
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
>
>
> --
> Randall Degges
> http://rdegges.com/
>
> --
> You received this message because you are subscribed to a topic in the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/L3Z8yDiFa7g/unsubscribe?hl=en.
> To unsubscribe from this group and all its topics, send an email to
> [email protected].
>
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
>
>
> --
> Randall Degges
> http://rdegges.com/
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
