Foreign Key having multiple columns mapping. How to do it in Gino syntax?

[neeraj-mindtickle]

• GINO version: 0.8.2
• Python version: 3.7.2
• asyncpg version:
• aiocontextvars version:
• PostgreSQL version:

Description

I have 2 base tables and One table having a foreign key relationship with those two tables.
raw sql query for creation of these tables:

CREATE TABLE 'theme_info' (
	'tenant_id' int8 NOT NULL,
	'theme_id' int8 NOT NULL,
	'theme_name' VARCHAR(255) NOT NULL,
	'description' text,
	'created_by' int8 NOT NULL DEFAULT 0,
	'updated_by' int8 NOT NULL DEFAULT 0,
	'created_at' TIMESTAMP NOT NULL DEFAULT now(),
	'updated_at' TIMESTAMP NOT NULL DEFAULT now(),
	PRIMARY KEY('tenant_id', 'theme_id')
);

CREATE TABLE 'keyword_info' (
	'tenant_id' int8 NOT NULL,
	'keyword_id' int8 NOT NULL,
	'keyword_text' VARCHAR(255),
	'created_at' TIMESTAMP NOT NULL DEFAULT now(),
	'updated_at' TIMESTAMP NOT NULL DEFAULT now(),
	PRIMARY KEY('tenant_id', 'keyword_id')
);

Table having dependency on these two tables:

CREATE table 'theme_keyword_map' (
	'tenant_id' int8 NOT NULL,
	'theme_id' int8 NOT NULL,
	'keyword_id' int8 NOT NULL,
	'created_at' TIMESTAMP NOT NULL DEFAULT now(),
	'updated_at' TIMESTAMP NOT NULL DEFAULT now(),
	PRIMARY KEY(tenant_id, theme_id, keyword_id),
	FOREIGN Key (tenant_id, theme_id) REFERENCES theme_info (tenant_id, theme_id),
	FOREIGN KEY (tenant_id, keyword_id) REFERENCES keyword_info (tenant_id, keyword_id)
);

I need to create these tables using Gino:

I am currently doing this to achieve this:

class Themes(BaseModel):
    __tablename__ = 'theme_info'
    tenant_id = db.Column(db.BigInteger(), nullable=False, primary_key=True)
    theme_id = db.Column(db.BigInteger(), nullable=False, primary_key=True)
    theme_name = db.Column(db.VARCHAR(255), nullable=False)
    description = db.Column(db.TEXT(), nullable=True)
    created_by = db.Column(db.BigInteger(), nullable=False)
    updated_by = db.Column(db.BigInteger(), nullable=False)
    created_at = db.Column(db.DateTime(), nullable=False)
    updated_at = db.Column(db.DateTime(), nullable=False)

class Keywords(BaseModel):
    __tablename__ = 'keyword_info'
    tenant_id = db.Column(db.BigInteger(), nullable=False, primary_key=True)
    keyword_id = db.Column(db.BigInteger(), nullable=False, primary_key=True)
    keyword_text = db.Column(db.VARCHAR(255), nullable=False)
    created_at = db.Column(db.DateTime(), nullable=False)

class ThemeKeywordMap(BaseModel):
    __tablename__ = 'theme_keyword_map_1'
    tenant_id = db.Column(db.BigInteger(), db.ForeignKey('theme_info.tenant_id', 'keyword_info.tenant_id'), nullable=False, primary_key=True)
    theme_id = db.Column(db.BigInteger(), db.ForeignKey('theme_info.theme_id'), nullable=False, primary_key=True)
    keyword_id = db.Column(db.BigInteger(), db.ForeignKey('keyword_info.keyword_id'), nullable=False, primary_key=True)
    created_at = db.Column(db.DateTime(), nullable=False)
    updated_at = db.Column(db.DateTime(), nullable=False)

But this is providing this error:

asyncpg.exceptions.InvalidForeignKeyError: there is no unique constraint matching given keys for referenced table "theme_info"

How to create this 3rd table with Gino?

[fantix]

You should use ForeignKeyConstraint like this:

class ThemeKeywordMap(BaseModel):
    __tablename__ = "theme_keyword_map_1"
    tenant_id = db.Column(db.BigInteger(), nullable=False, primary_key=True)
    theme_id = db.Column(db.BigInteger(), nullable=False, primary_key=True)
    keyword_id = db.Column(db.BigInteger(), nullable=False, primary_key=True)
    created_at = db.Column(db.DateTime(), nullable=False)
    updated_at = db.Column(db.DateTime(), nullable=False)

    themes_fk = db.ForeignKeyConstraint(
        ["tenant_id", "theme_id"], ["theme_info.tenant_id", "theme_info.theme_id"]
    )
    keywords_fk = db.ForeignKeyConstraint(
        ["tenant_id", "keyword_id"],
        ["keyword_info.tenant_id", "keyword_info.keyword_id"],
    )

This generates:

CREATE TABLE theme_keyword_map_1 (
	tenant_id BIGINT NOT NULL,
	theme_id BIGINT NOT NULL,
	keyword_id BIGINT NOT NULL,
	created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
	updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
	PRIMARY KEY (tenant_id, theme_id, keyword_id),
	FOREIGN KEY(tenant_id, theme_id) REFERENCES theme_info (tenant_id, theme_id),
	FOREIGN KEY(tenant_id, keyword_id) REFERENCES keyword_info (tenant_id, keyword_id)
)

Because:

It’s important to note that the ForeignKeyConstraint is the only way to define a composite foreign key. While we could also have placed individual ForeignKey objects on both the invoice_item.invoice_id and invoice_item.ref_num columns, SQLAlchemy would not be aware that these two values should be paired together - it would be two individual foreign key constraints instead of a single composite foreign key referencing two columns.

[wwwjfy]

Closing due to inactivity. Feel free to reopen if there is further clarification needed.

[gunkow]

@fantix es solution works:

class Parent(db.Model):
    __tablename__ = 'parent'
    id = db.Column(db.Integer, primary_key=True)
    id2 = db.Column(db.Integer, primary_key=True)


class Child(db.Model):
    __tablename__ = 'child'
    pid = db.Column(db.Integer, primary_key=True)
    pid2 = db.Column(db.Integer, primary_key=True)
    fk = db.ForeignKeyConstraint(['pid', 'pid2'], ['parent.id', 'parent.id2'], name="fk")

GINO version: 0.8.6
postgres 11