Hi all,
In my DB model (We can have multiple GTdatasets where each dataset can have multiple versions where each version in-turn can have multiple images ) we have multiple foreign keys (version and id ) in gtdataset images table referencing to the version table.In version table , only version is not unique but combination of datsetid and version is unique . How can we define such relationships. I am really struggling to find the solution. If someone has faced this kind of problem , it will be helpful. class GTDataSet(db.Model): __tablename__ = 'gtdataset' id = db.Column(db.Integer, primary_key=True, autoincrement=True) gtdatasetid = db.Column(db.String(50), primary_key=True,unique=True) gtdataset_category = db.Column(db.String(50)) gtdataset_name = db.Column(db.Text) gtdatasetversion = db.relationship('GTDataSetVersion', backref='gtdataset',lazy='joined') class GTDataSetVersion(db.Model): __tablename__ = 'gtdataset_version' version = db.Column(db.String(5), primary_key=True) gtdatasetid = db.Column(db.String(50), db.ForeignKey('gtdataset.gtdatasetid',ondelete='CASCADE'),primary_key=True) register_user = db.Column(db.String(50)) register_date = db.Column(db.DateTime) last_update_user = db.Column(db.String(50)) last_update_date = db.Column(db.DateTime) images = db.relationship('GTDataSetImages', backref='gtdataset_version',lazy='joined', \ primaryjoin = "GTDataSetVersion.version==GTDataSetImages.version") class GTDataSetImages(SoftDeletionModel): __tablename__ = 'gtdataset_images' id = db.Column(db.Integer, primary_key=True, autoincrement=True) image_id = db.Column(db.String(50), db.ForeignKey('images.image_id'), primary_key=True) register_user = db.Column(db.String(50)) register_date = db.Column(db.DateTime) image = db.relationship('Images', backref='gtdataset_images', lazy='joined') version = db.Column(db.String(5), db.ForeignKey('gtdataset_version.version'), primary_key=True) gtdatasetversion = db.relationship('GTDataSetVersion', foreign_keys=[version], backref='version_gtdataset_images', lazy='joined') gtid = db.Column(db.String(50), db.ForeignKey('gtdataset_version.gtdatasetid'), primary_key=True) gtdataid = db.relationship('GTDataSetVersion', foreign_keys=[gtid], backref='id_gtdataset_images', lazy='joined') Error :- sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "gtdataset_version" [SQL: CREATE TABLE gtdataset_images ( deleted_at TIMESTAMP WITH TIME ZONE, id SERIAL NOT NULL, image_id VARCHAR(50) NOT NULL, register_user VARCHAR(50), register_date TIMESTAMP WITHOUT TIME ZONE, version VARCHAR(5) NOT NULL, gtid VARCHAR(50) NOT NULL, PRIMARY KEY (id, image_id, version, gtid), FOREIGN KEY(image_id) REFERENCES images (image_id), FOREIGN KEY(version) REFERENCES gtdataset_version (version), FOREIGN KEY(gtid) REFERENCES gtdataset_version (gtdatasetid) ) ] (Background on this error at: http://sqlalche.me/e/f405) -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c2a0b20c-fef3-4825-b23b-5b863f187a91%40googlegroups.com.