shop_platform - Composite Foreign Keys

想要達到的效果：

現有 order_item 這張表。現在要建立 rating table ，並且建立 order_item 和 rating 之間的 one-one relationship。但 order_item 的 primary key 是 composite primary key，同時有兩個 column 都是 primary key。不知道要怎麼在 rating 建立外鍵。

order_item 的程式碼：

from app import db
from datetime import datetime


class OrderItem(db.Model):
    __tablename__ = 'order_item'

    order_id = db.Column(db.Integer, db.ForeignKey('order.id'), primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), primary_key=True)
    price = db.Column(db.Integer, nullable=False)
    quantity = db.Column(db.Integer,  nullable=False)
    insert_time = db.Column(db.DateTime, nullable=False, default=datetime.now)
    update_time = db.Column(db.DateTime, onupdate=datetime.now, nullable=False, default=datetime.now)

    order = db.relationship('Order', back_populates='products')
    product = db.relationship('Product', back_populates='orders')

參考資料

用「sqlalchemy foreign key two primary key」當關鍵字，搜尋到 Composite Foreign Keys and Many-to-Many Relationships in SQLAlchemy 這篇文章

成果

order_item 的程式碼：

from app import db
from datetime import datetime


class OrderItem(db.Model):
    __tablename__ = 'order_item'

    order_id = db.Column(db.Integer, db.ForeignKey('order.id'), primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), primary_key=True)
    price = db.Column(db.Integer, nullable=False)
    quantity = db.Column(db.Integer,  nullable=False)
    insert_time = db.Column(db.DateTime, nullable=False, default=datetime.now)
    update_time = db.Column(db.DateTime, onupdate=datetime.now, nullable=False, default=datetime.now)

    order = db.relationship('Order', back_populates='products')
    product = db.relationship('Product', back_populates='orders')
    rating = db.relationship('Rating', back_populates='order_item', uselist=False)

rating 的程式碼：

from app import db
from datetime import datetime


class Rating(db.Model):
    __tablename__ = 'rating'

    id = db.Column(db.Integer, primary_key=True)
    score = db.Column(db.SmallInteger, nullable=False)
    comment = db.Column(db.String(2000))
    insert_time = db.Column(db.DateTime, nullable=False, default=datetime.now)
    update_time = db.Column(db.DateTime, onupdate=datetime.now, nullable=False, default=datetime.now)
    rater_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    ratee_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    order_item_order_id = db.Column(db.Integer, nullable=False)
    order_item_product_id = db.Column(db.Integer, nullable=False)

    __table_args__ = (
        db.ForeignKeyConstraint(
            ['order_item_order_id', 'order_item_product_id'],
            ['order_item.order_id', 'order_item.product_id']
        ),
    )

    rater = db.relationship('User', back_populates="rating_record")
    ratee = db.relationship('User', back_populates="rated_record")
    order_item = db.relationship('OrderItem', back_populates="rating")

commit: feat: add Rating model