From be508c2d920a46369e4fb0b89a984f0030938c8b Mon Sep 17 00:00:00 2001 From: Billy Chan Date: Mon, 29 Apr 2024 17:57:06 +0800 Subject: [PATCH 1/2] Postgres: allow foreign key column without unique constraint --- src/postgres/query/constraints/mod.rs | 16 ++++++++-- tests/live/postgres/src/main.rs | 44 +++++++++++++++++++++++++++ 2 files changed, 57 insertions(+), 3 deletions(-) diff --git a/src/postgres/query/constraints/mod.rs b/src/postgres/query/constraints/mod.rs index fdc53ad..1369762 100644 --- a/src/postgres/query/constraints/mod.rs +++ b/src/postgres/query/constraints/mod.rs @@ -188,9 +188,19 @@ impl SchemaQueryBuilder { .equals((rcsq.clone(), RefC::ConstraintName)), ) .add( - // Only join when the referenced primary key position matches position_in_unique_constraint for the foreign key column - Expr::col((Schema::KeyColumnUsage, Kcuf::PositionInUniqueConstraint)) - .equals((rcsq.clone(), Kcuf::OrdinalPosition)), + Condition::any() + .add( + // Only join when the referenced primary key position matches position_in_unique_constraint for the foreign key column + Expr::col(( + Schema::KeyColumnUsage, + Kcuf::PositionInUniqueConstraint, + )) + .equals((rcsq.clone(), Kcuf::OrdinalPosition)), + ) + .add( + // Allow foreign key column without unique constraint + Expr::col((rcsq.clone(), Kcuf::OrdinalPosition)).is_null(), + ), ), ) .and_where( diff --git a/tests/live/postgres/src/main.rs b/tests/live/postgres/src/main.rs index 60889c6..ede43b8 100644 --- a/tests/live/postgres/src/main.rs +++ b/tests/live/postgres/src/main.rs @@ -55,6 +55,8 @@ async fn main() { create_parent_table(), create_child_table(), create_db_types_table(), + create_table1(), + create_table2(), ]; for tbl_create_stmt in tbl_create_stmts.iter() { @@ -62,6 +64,15 @@ async fn main() { println!("{};", sql); println!(); sqlx::query(&sql).execute(&mut *executor).await.unwrap(); + if sql.starts_with(r#"CREATE TABLE "table1""#) { + let sql = Index::create() + .table(Alias::new("table1")) + .name("IDX_table1_unique_u") + .col(Alias::new("u")) + .unique() + .to_string(PostgresQueryBuilder); + sqlx::query(&sql).execute(&mut *executor).await.unwrap(); + } } let schema_discovery = SchemaDiscovery::new(connection, "public"); @@ -456,3 +467,36 @@ fn create_db_types_table() -> TableCreateStatement { ) .to_owned() } + +fn create_table1() -> TableCreateStatement { + Table::create() + .table(Alias::new("table1")) + .col( + ColumnDef::new(Alias::new("id")) + .integer() + .not_null() + .auto_increment(), + ) + .col(ColumnDef::new(Alias::new("u")).integer().not_null()) + .to_owned() +} + +fn create_table2() -> TableCreateStatement { + Table::create() + .table(Alias::new("table2")) + .col( + ColumnDef::new(Alias::new("fk_u")) + .integer() + .not_null() + .auto_increment(), + ) + .foreign_key( + ForeignKey::create() + .name("FK_tabl2_table1") + .from(Alias::new("table2"), Alias::new("fk_u")) + .to(Alias::new("table1"), Alias::new("u")) + .on_delete(ForeignKeyAction::Cascade) + .on_update(ForeignKeyAction::Cascade), + ) + .to_owned() +} From 98b253455dfb8b2622f048fc79936824fcbe7942 Mon Sep 17 00:00:00 2001 From: Billy Chan Date: Tue, 30 Apr 2024 14:14:51 +0800 Subject: [PATCH 2/2] rename --- tests/live/postgres/src/main.rs | 24 ++++++++++++------------ 1 file changed, 12 insertions(+), 12 deletions(-) diff --git a/tests/live/postgres/src/main.rs b/tests/live/postgres/src/main.rs index ede43b8..4782cf7 100644 --- a/tests/live/postgres/src/main.rs +++ b/tests/live/postgres/src/main.rs @@ -55,8 +55,8 @@ async fn main() { create_parent_table(), create_child_table(), create_db_types_table(), - create_table1(), - create_table2(), + create_fkey_parent_table(), + create_fkey_child_table(), ]; for tbl_create_stmt in tbl_create_stmts.iter() { @@ -64,10 +64,10 @@ async fn main() { println!("{};", sql); println!(); sqlx::query(&sql).execute(&mut *executor).await.unwrap(); - if sql.starts_with(r#"CREATE TABLE "table1""#) { + if sql.starts_with(r#"CREATE TABLE "fkey_parent_table""#) { let sql = Index::create() - .table(Alias::new("table1")) - .name("IDX_table1_unique_u") + .table(Alias::new("fkey_parent_table")) + .name("IDX_fkey_parent_table_unique_u") .col(Alias::new("u")) .unique() .to_string(PostgresQueryBuilder); @@ -468,9 +468,9 @@ fn create_db_types_table() -> TableCreateStatement { .to_owned() } -fn create_table1() -> TableCreateStatement { +fn create_fkey_parent_table() -> TableCreateStatement { Table::create() - .table(Alias::new("table1")) + .table(Alias::new("fkey_parent_table")) .col( ColumnDef::new(Alias::new("id")) .integer() @@ -481,9 +481,9 @@ fn create_table1() -> TableCreateStatement { .to_owned() } -fn create_table2() -> TableCreateStatement { +fn create_fkey_child_table() -> TableCreateStatement { Table::create() - .table(Alias::new("table2")) + .table(Alias::new("fkey_child_table")) .col( ColumnDef::new(Alias::new("fk_u")) .integer() @@ -492,9 +492,9 @@ fn create_table2() -> TableCreateStatement { ) .foreign_key( ForeignKey::create() - .name("FK_tabl2_table1") - .from(Alias::new("table2"), Alias::new("fk_u")) - .to(Alias::new("table1"), Alias::new("u")) + .name("FK_tabl2_fkey_parent_table") + .from(Alias::new("fkey_child_table"), Alias::new("fk_u")) + .to(Alias::new("fkey_parent_table"), Alias::new("u")) .on_delete(ForeignKeyAction::Cascade) .on_update(ForeignKeyAction::Cascade), )