SQLAlchemy one-to-many without the child table having a primary key

Question

Is it possible to create a table without a primary key in SQLAlchemy? The relationship I want to define is as follows:

class TPost(Base):
  __tablename__ = "forum_post"
  id = Column(Integer, primary_key = True)
  topic_id = Column(Integer, ForeignKey("forum_topic.id"))
  index = Column(Integer)
  page = Column(Integer)
  user_id = Column(Integer, ForeignKey("forum_user.id"))
  posted_at = Column(DateTime)
  post_text = Column(String)
  has_quotes = Column(Boolean)
  quotes = relationship("TQuote")

class TQuote(Base):
  __tablename__ = "forum_quotes"
  id = Column(Integer, ForeignKey("forum_post.id"))
  is_direct = Column(Boolean)
  quoted_text = Column(String)
  quoted_id = Column(Integer)   

As you can see I don't really need a primary key, and I don't intend to extend the Quote relationship in the future.

My problem specifically is represented by this error message :

sqlalchemy.exc.ArgumentError: Mapper Mapper|TQuote|forum_quotes 
could not assemble any primary key columns for mapped table 'forum_quotes'

edit : The (id,quoted_id) pair is unique, and it present for the majority of the data, however when the quote is not direct(and doesn't have a quoted_id in that case), I inline the quoted text directly into the quote relationship. I could use a dual table approach (where the indrect quotes have a table with a primary key), but I'd really rather implement this as a single one-to-many relationship. I don't want to have to do more than a single join.

edit 2:

I'll number the quotes and use the foreign-key + app generated number as a pkey, still annoying tho. Now to figure out the syntax.

edit 3:

Solved the problem as outlined in edit 2. Quite annoyed with sql alchemy since it has all the information it needs to implement the relatioship even when modelling the data at a high level. I understand the reasons why Sql Alchemy wants to have a primary key (makes the orm easier to implement).

I am beginning to question why I am using Sql Alchemy, without it I could implement one way UPSERT or CREATE_IF_NOT_EXIST asynchronous operations using psycopg2. ORM's really need to catch up.

Answer 1

I am assuming @TokenMacGuy is right, and you really are confusing the notions of PrimaryKey, and a surrogate key. In which case the answer to your question is:

• NO, SA does not support tables (and therefore relations to tables) without a primary key
• and NO, you do not need to create a surrogate key for each table for the purpose of serving as a primary key. You can define a PK using any combination of columns with are unique.

See the code below for an example:

class TPost(Base):
    __tablename__ = 'forum_post'
    id = Column(Integer, primary_key = True)
    post_text = Column(String)
    quotes = relationship("TQuote", backref="post")

class TQuote(Base):
    __tablename__ = "forum_quotes"
    id = Column(Integer, ForeignKey("forum_post.id"))
    is_direct = Column(Boolean)
    quoted_text = Column(String)
    quoted_id = Column(Integer) 
    __table_args__ = (PrimaryKeyConstraint(id, quoted_id),)