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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c2a0b20c-fef3-4825-b23b-5b863f187a91%40googlegroups.com.
