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 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
>
> <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/f2d07a43-388b-47b5-9cb3-917d96c97ee6%40www.fastmail.com.
