Thanks a lot for response and after following the suggestion, issue got resolved . As you have mentioned we need to use ForeignKeyConstraint class.
Thanks once again. Regards Nitin On Saturday, 5 October 2019 19:28:08 UTC+5:30, Mike Bayer wrote: > > if GTDataSetVersion -> GTDataSetImages is one to many, then > GTDataSetImages needs to have a foreign key to the primary key of > GTDataSetVersion. GTDataSetVersion.gtdatasetid is only part of its primary > key and is not unique. you need a composite foriegn key to > GTDataSetVersion(version, gtdataset_id). Composite foreign keys must use > the ForeignKeyConstraint class are illustrated at > https://docs.sqlalchemy.org/en/13/core/constraints.html#defining-foreign-keys > > . For background on how to use these with declarative mappings see > https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html#table-configuration > > . > > On Sat, Oct 5, 2019, at 7:09 AM, Nitin Jain wrote: > > 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 sqlal...@googlegroups.com <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/c2a0b20c-fef3-4825-b23b-5b863f187a91%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c2a0b20c-fef3-4825-b23b-5b863f187a91%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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/4e30b28e-df69-4309-8385-6ad9f5089d91%40googlegroups.com.
