0

This might be a dumb question, but I can't find anything that indicates that you can't, however, when I try it, it does not work. I have an example of such an instance, but I can't reproduce the results.

Using SQLAlchemy, here is the working instance:

class Commissions(Base):
    __tablename__ = "commissions"
    id = Column(
        Integer,
        Sequence("commission_id", optional=True),
        primary_key=True
    )
    commission_period_name = Column(
        Unicode(40), ForeignKey("commission_periods.name"))
    commission_period = relationship(
        "CommissionPeriods",
        primaryjoin="CommissionPeriods.name == Commissions.commission_period_name",
        uselist=False,
        backref="commissions"
    )
    agent_id = Column(Integer, ForeignKey("agents.id"))
    agent = relationship(
        Agents,
        primaryjoin=Agents.id == agent_id,
        uselist=False,
        backref="commissions"
    )
    create_stamp = Column(DateTime)
    commission_type = Column(Unicode(40))
    amount = Column(Numeric(10, 2))
    transactions_id = Column(Integer, ForeignKey(Transactions.id))
    transaction = relationship(Transactions, primaryjoin=Transactions.id ==
                               transactions_id, backref="commissions", uselist=False)

Note the commission_period uses name, not an id as reference.

Here is the CommissionPeriods table definition:

class CommissionPeriods(Base):
    __tablename__ = "commission_periods"
    id = Column(
        Integer,
        Sequence("commission_periods_id", optional=True),
        primary_key=True
    )
    name = Column(Unicode(40), index=True)
    start_date = Column(DateTime(), index=True)
    end_date = Column(DateTime(), index=True)
    network = Column(Unicode(40), index=True)
    status = Column(Unicode(40), index=True)
    created_by = Column(Unicode(40))
    create_stamp = Column(DateTime())
    modify_stamp = Column(DateTime())

The alembic scripts runs without errors.

I have tried to replicate the results, with different tables (more or less the same table structures, with a name column that I am trying to use as a FK), but I have had no luck - on the python side everything is okay, but once I try to run the alembic scripts it tells me that the Foreign key is incorrectly formed.

Can someone please explain to me how this works, why it works in the given instance but not when I try to replicate the results from the above?

This is what I have tried in replicating the results:

class Networks(Base):
    __tablename__ = "networks"
    id = Column(
        Integer,
        Sequence('networks_id', optional=True),
        primary_key=True
    )
    name = Column(Unicode(40), index=True)
    rica_name = Column(Unicode(40))
    net_iccid_start = Column(Integer)
    net_iccid_end = Column(Integer)
    net_iccid_int = Column(Integer)
    network_class = Column(Unicode(60))

    _config = Column("config", Unicode(2048))

Note that in the above table I want to use the name column as foreign key in:

class AgentRecharges(Base):
    __tablename__ = "agent_recharges"
    id = Column(
        Integer,
        Sequence('agent_recharges_id', optional=True),
        primary_key=True
    )
    status = Column(Unicode(40))
    create_stamp = Column(DateTime, index=True)
    create_by = Column(Integer, ForeignKey(Agents.id))
    create_by_agent = relationship(
        Agents, primaryjoin=Agents.id == create_by, uselist=False)
    modify_stamp = Column(DateTime, index=True)
    complete_stamp = Column(DateTime, index=True)
    msisdn = Column(Unicode(20), index=True, nullable=False)
    amount = Column(Float, index=True, nullable=False)
    network_name = Column(Unicode(40), ForeignKey(
        "networks.name"), nullable=False)
    network = relationship(
        "Networks", primaryjoin="Networks.name == AgentRecharges.network_name", uselist=False)
    iccid = Column(Unicode(40))
    sim = relationship(Sims, backref="agent_recharges")
    agents_id = Column(Integer, ForeignKey(Agents.id))
    agent = relationship(Agents, primaryjoin=Agents.id ==
                         agents_id, uselist=False)
    transactions_id = Column(Integer, ForeignKey(Transactions.id))
    transaction = relationship(Transactions, primaryjoin=Transactions.id ==
                               transactions_id, backref="agent_recharges", uselist=False)
    recharge_batch_id = Column(Integer, ForeignKey(RechargeBatches.id))
    recharge_batch = relationship(RechargeBatches)

When I run the alembic script to add this new table, it tells me that the foreign key is incorrectly formed.

Any ideas on why, and how I can accomplish my goal?

Renier
  • 1,523
  • 4
  • 32
  • 60
  • The referenced column doesn't have to be a primary key, but it does have to be indexed. – Barmar Apr 13 '16 at 16:31
  • You have `index = True` for the `name` column in the above example, make sure you do that when you're replicating. – Barmar Apr 13 '16 at 16:32
  • @Barmar, the referenced column (or columns) must be `UNIQUE` and `NOT NULL`. A simple index is not eough. – Paul Spiegel Apr 13 '16 at 16:43
  • @PaulSpiegel InnoDB allows it as an extension, see http://stackoverflow.com/questions/588741/can-a-foreign-key-reference-a-non-unique-index – Barmar Apr 13 '16 at 16:44
  • @Barmar, that's strange - thanks for info. I should have been skeptical to my "knowlege", reading that comment from you. – Paul Spiegel Apr 13 '16 at 16:55
  • @PaulSpiegel Don't be shy, I do make misteaks sometimes. – Barmar Apr 13 '16 at 16:59
  • @Barmar and @ PaulSpiegel Thank you both for your responses. as I mentioned in the question I have tried to replicate this - every thing in my attempt is structured exactly the same, but it does not work. I'll update my question to show what I have tried in replicating the results. – Renier Apr 14 '16 at 06:25

0 Answers0