How Can I Build a ForeignKey to a Table Which Has Multiple Primary Keys?

[Randall Degges]

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.

[Michael Bayer]

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.

--
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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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]

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

--
Randall Degges

http://rdegges.com/

[Lloyd Kvam]

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.

[Simon King]

I don't understand your model. Can you have multiple rows in the
Exchange table which all have the same value for Exchange.exchange?

If so, and if you want PhoneNumber to be able to point to a single one
of those rows, then it needs 2 columns to do that (one to point to
Exchange.exchange and one to point at Exchange.area_code_pk).

If not (ie. Exchange.exchange uniquely identifies a row in the
Exchange table), then you probably don't want to make
Exchange.area_code_pk as a primary key. Note: it doesn't actually
matter whether it is declared in the database as a primary key.
SQLAlchemy only requires that the primary key uniquely identifies the
row in the database.

Simon

[Randall Degges]

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

--
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 sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Simon King]

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

[Randall Degges]

Hi Simon,

Ok cool. So, I updated that, but now I'm getting the following error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'phonenumbers.exchange_exchange' could not find table 'exchange' with which to generate a foreign key to target column 'exchange'

It looks like SQLa is trying to create the tables out of order (I'm guessing?). Is there a way to force this to execute in a specific order?

-Randall

[Simon King]

According to the pastie log, your table is called "exchanges", not "exchange", so the target columns should be called exchanges.exchange and exchanges.area_code_pk.

Simon

[Randall Degges]

Simon,

Thanks man! This works perfectly, can't believe I didn't see that.

This was actually a really frustrating experience, you guys have been extremely helpful. Thank you all so much!

Best,

-Randall