vespertide_query/sql/
remove_constraint.rs

1use sea_query::{Alias, ForeignKey, Query, Table};
2
3use vespertide_core::{TableConstraint, TableDef};
4
5use super::create_table::build_create_table_for_backend;
6use super::rename_table::build_rename_table;
7use super::types::{BuiltQuery, DatabaseBackend};
8use crate::error::QueryError;
9use crate::sql::RawSql;
10
11pub fn build_remove_constraint(
12    backend: &DatabaseBackend,
13    table: &str,
14    constraint: &TableConstraint,
15    current_schema: &[TableDef],
16) -> Result<Vec<BuiltQuery>, QueryError> {
17    match constraint {
18        TableConstraint::PrimaryKey { .. } => {
19            if *backend == DatabaseBackend::Sqlite {
20                // SQLite does not support dropping primary key constraints, use temp table approach
21                let table_def = current_schema.iter().find(|t| t.name == table).ok_or_else(|| QueryError::Other(format!("Table '{}' not found in current schema. SQLite requires current schema information to remove constraints.", table)))?;
22
23                // Remove the primary key constraint
24                let mut new_constraints = table_def.constraints.clone();
25                new_constraints.retain(|c| !matches!(c, TableConstraint::PrimaryKey { .. }));
26
27                // Generate temporary table name
28                let temp_table = format!("{}_temp", table);
29
30                // 1. Create temporary table without primary key constraint
31                let create_temp_table = build_create_table_for_backend(
32                    backend,
33                    &temp_table,
34                    &table_def.columns,
35                    &new_constraints,
36                );
37                let create_query = BuiltQuery::CreateTable(Box::new(create_temp_table));
38
39                // 2. Copy data (all columns)
40                let column_aliases: Vec<Alias> = table_def
41                    .columns
42                    .iter()
43                    .map(|c| Alias::new(&c.name))
44                    .collect();
45                let mut select_query = Query::select();
46                for col_alias in &column_aliases {
47                    select_query = select_query.column(col_alias.clone()).to_owned();
48                }
49                select_query = select_query.from(Alias::new(table)).to_owned();
50
51                let insert_stmt = Query::insert()
52                    .into_table(Alias::new(&temp_table))
53                    .columns(column_aliases.clone())
54                    .select_from(select_query)
55                    .unwrap()
56                    .to_owned();
57                let insert_query = BuiltQuery::Insert(Box::new(insert_stmt));
58
59                // 3. Drop original table
60                let drop_table = Table::drop().table(Alias::new(table)).to_owned();
61                let drop_query = BuiltQuery::DropTable(Box::new(drop_table));
62
63                // 4. Rename temporary table to original name
64                let rename_query = build_rename_table(&temp_table, table);
65
66                // 5. Recreate indexes from Index constraints
67                let mut index_queries = Vec::new();
68                for constraint in &table_def.constraints {
69                    if let TableConstraint::Index {
70                        name: idx_name,
71                        columns: idx_cols,
72                    } = constraint
73                    {
74                        let index_name = vespertide_naming::build_index_name(
75                            table,
76                            idx_cols,
77                            idx_name.as_deref(),
78                        );
79                        let mut idx_stmt = sea_query::Index::create();
80                        idx_stmt = idx_stmt.name(&index_name).to_owned();
81                        for col_name in idx_cols {
82                            idx_stmt = idx_stmt.col(Alias::new(col_name)).to_owned();
83                        }
84                        idx_stmt = idx_stmt.table(Alias::new(table)).to_owned();
85                        index_queries.push(BuiltQuery::CreateIndex(Box::new(idx_stmt)));
86                    }
87                }
88
89                let mut queries = vec![create_query, insert_query, drop_query, rename_query];
90                queries.extend(index_queries);
91                Ok(queries)
92            } else {
93                // Other backends: use raw SQL
94                let pg_sql = format!(
95                    "ALTER TABLE \"{}\" DROP CONSTRAINT \"{}_pkey\"",
96                    table, table
97                );
98                let mysql_sql = format!("ALTER TABLE `{}` DROP PRIMARY KEY", table);
99                Ok(vec![BuiltQuery::Raw(RawSql::per_backend(
100                    pg_sql.clone(),
101                    mysql_sql,
102                    pg_sql,
103                ))])
104            }
105        }
106        TableConstraint::Unique { name, columns } => {
107            // SQLite does not support ALTER TABLE ... DROP CONSTRAINT UNIQUE
108            if *backend == DatabaseBackend::Sqlite {
109                // Use temporary table approach for SQLite
110                let table_def = current_schema.iter().find(|t| t.name == table).ok_or_else(|| QueryError::Other(format!("Table '{}' not found in current schema. SQLite requires current schema information to remove constraints.", table)))?;
111
112                // Create new constraints without the removed unique constraint
113                let mut new_constraints = table_def.constraints.clone();
114                new_constraints.retain(|c| {
115                    match (c, constraint) {
116                        (
117                            TableConstraint::Unique {
118                                name: c_name,
119                                columns: c_cols,
120                            },
121                            TableConstraint::Unique {
122                                name: r_name,
123                                columns: r_cols,
124                            },
125                        ) => {
126                            // Remove if names match, or if no name and columns match
127                            if let (Some(cn), Some(rn)) = (c_name, r_name) {
128                                cn != rn
129                            } else {
130                                c_cols != r_cols
131                            }
132                        }
133                        _ => true,
134                    }
135                });
136
137                // Generate temporary table name
138                let temp_table = format!("{}_temp", table);
139
140                // 1. Create temporary table without the removed constraint
141                let create_temp_table = build_create_table_for_backend(
142                    backend,
143                    &temp_table,
144                    &table_def.columns,
145                    &new_constraints,
146                );
147                let create_query = BuiltQuery::CreateTable(Box::new(create_temp_table));
148
149                // 2. Copy data (all columns)
150                let column_aliases: Vec<Alias> = table_def
151                    .columns
152                    .iter()
153                    .map(|c| Alias::new(&c.name))
154                    .collect();
155                let mut select_query = Query::select();
156                for col_alias in &column_aliases {
157                    select_query = select_query.column(col_alias.clone()).to_owned();
158                }
159                select_query = select_query.from(Alias::new(table)).to_owned();
160
161                let insert_stmt = Query::insert()
162                    .into_table(Alias::new(&temp_table))
163                    .columns(column_aliases.clone())
164                    .select_from(select_query)
165                    .unwrap()
166                    .to_owned();
167                let insert_query = BuiltQuery::Insert(Box::new(insert_stmt));
168
169                // 3. Drop original table
170                let drop_table = Table::drop().table(Alias::new(table)).to_owned();
171                let drop_query = BuiltQuery::DropTable(Box::new(drop_table));
172
173                // 4. Rename temporary table to original name
174                let rename_query = build_rename_table(&temp_table, table);
175
176                // 5. Recreate indexes from Index constraints
177                let mut index_queries = Vec::new();
178                for c in &table_def.constraints {
179                    if let TableConstraint::Index {
180                        name: idx_name,
181                        columns: idx_cols,
182                    } = c
183                    {
184                        let index_name = vespertide_naming::build_index_name(
185                            table,
186                            idx_cols,
187                            idx_name.as_deref(),
188                        );
189                        let mut idx_stmt = sea_query::Index::create();
190                        idx_stmt = idx_stmt.name(&index_name).to_owned();
191                        for col_name in idx_cols {
192                            idx_stmt = idx_stmt.col(Alias::new(col_name)).to_owned();
193                        }
194                        idx_stmt = idx_stmt.table(Alias::new(table)).to_owned();
195                        index_queries.push(BuiltQuery::CreateIndex(Box::new(idx_stmt)));
196                    }
197                }
198
199                let mut queries = vec![create_query, insert_query, drop_query, rename_query];
200                queries.extend(index_queries);
201                Ok(queries)
202            } else {
203                // For unique constraints created via CREATE UNIQUE INDEX (PostgreSQL/SQLite),
204                // we use DROP INDEX. MySQL uses ALTER TABLE DROP INDEX.
205                let constraint_name = vespertide_naming::build_unique_constraint_name(
206                    table,
207                    columns,
208                    name.as_deref(),
209                );
210                // PostgreSQL and SQLite: DROP INDEX (matches how we create them)
211                // MySQL: ALTER TABLE DROP INDEX
212                let pg_sql = format!("DROP INDEX \"{}\"", constraint_name);
213                let mysql_sql = format!("ALTER TABLE `{}` DROP INDEX `{}`", table, constraint_name);
214                let sqlite_sql = format!("DROP INDEX \"{}\"", constraint_name);
215                Ok(vec![BuiltQuery::Raw(RawSql::per_backend(
216                    pg_sql, mysql_sql, sqlite_sql,
217                ))])
218            }
219        }
220        TableConstraint::ForeignKey { name, columns, .. } => {
221            // SQLite does not support ALTER TABLE ... DROP CONSTRAINT FOREIGN KEY
222            if *backend == DatabaseBackend::Sqlite {
223                // Use temporary table approach for SQLite
224                let table_def = current_schema.iter().find(|t| t.name == table).ok_or_else(|| QueryError::Other(format!("Table '{}' not found in current schema. SQLite requires current schema information to remove constraints.", table)))?;
225
226                // Create new constraints without the removed foreign key constraint
227                let mut new_constraints = table_def.constraints.clone();
228                new_constraints.retain(|c| {
229                    match (c, constraint) {
230                        (
231                            TableConstraint::ForeignKey {
232                                name: c_name,
233                                columns: c_cols,
234                                ..
235                            },
236                            TableConstraint::ForeignKey {
237                                name: r_name,
238                                columns: r_cols,
239                                ..
240                            },
241                        ) => {
242                            // Remove if names match, or if no name and columns match
243                            if let (Some(cn), Some(rn)) = (c_name, r_name) {
244                                cn != rn
245                            } else {
246                                c_cols != r_cols
247                            }
248                        }
249                        _ => true,
250                    }
251                });
252
253                // Generate temporary table name
254                let temp_table = format!("{}_temp", table);
255
256                // 1. Create temporary table without the removed constraint
257                let create_temp_table = build_create_table_for_backend(
258                    backend,
259                    &temp_table,
260                    &table_def.columns,
261                    &new_constraints,
262                );
263                let create_query = BuiltQuery::CreateTable(Box::new(create_temp_table));
264
265                // 2. Copy data (all columns)
266                let column_aliases: Vec<Alias> = table_def
267                    .columns
268                    .iter()
269                    .map(|c| Alias::new(&c.name))
270                    .collect();
271                let mut select_query = Query::select();
272                for col_alias in &column_aliases {
273                    select_query = select_query.column(col_alias.clone()).to_owned();
274                }
275                select_query = select_query.from(Alias::new(table)).to_owned();
276
277                let insert_stmt = Query::insert()
278                    .into_table(Alias::new(&temp_table))
279                    .columns(column_aliases.clone())
280                    .select_from(select_query)
281                    .unwrap()
282                    .to_owned();
283                let insert_query = BuiltQuery::Insert(Box::new(insert_stmt));
284
285                // 3. Drop original table
286                let drop_table = Table::drop().table(Alias::new(table)).to_owned();
287                let drop_query = BuiltQuery::DropTable(Box::new(drop_table));
288
289                // 4. Rename temporary table to original name
290                let rename_query = build_rename_table(&temp_table, table);
291
292                // 5. Recreate indexes from Index constraints
293                let mut index_queries = Vec::new();
294                for c in &table_def.constraints {
295                    if let TableConstraint::Index {
296                        name: idx_name,
297                        columns: idx_cols,
298                    } = c
299                    {
300                        let index_name = vespertide_naming::build_index_name(
301                            table,
302                            idx_cols,
303                            idx_name.as_deref(),
304                        );
305                        let mut idx_stmt = sea_query::Index::create();
306                        idx_stmt = idx_stmt.name(&index_name).to_owned();
307                        for col_name in idx_cols {
308                            idx_stmt = idx_stmt.col(Alias::new(col_name)).to_owned();
309                        }
310                        idx_stmt = idx_stmt.table(Alias::new(table)).to_owned();
311                        index_queries.push(BuiltQuery::CreateIndex(Box::new(idx_stmt)));
312                    }
313                }
314
315                let mut queries = vec![create_query, insert_query, drop_query, rename_query];
316                queries.extend(index_queries);
317                Ok(queries)
318            } else {
319                // Build foreign key drop using ForeignKey::drop()
320                let constraint_name =
321                    vespertide_naming::build_foreign_key_name(table, columns, name.as_deref());
322                let fk_drop = ForeignKey::drop()
323                    .name(&constraint_name)
324                    .table(Alias::new(table))
325                    .to_owned();
326                Ok(vec![BuiltQuery::DropForeignKey(Box::new(fk_drop))])
327            }
328        }
329        TableConstraint::Index { name, columns } => {
330            // Index constraints are simple DROP INDEX statements for all backends
331            let index_name = if let Some(n) = name {
332                // Use naming convention for named indexes
333                vespertide_naming::build_index_name(table, columns, Some(n))
334            } else {
335                // Generate name from table and columns for unnamed indexes
336                vespertide_naming::build_index_name(table, columns, None)
337            };
338            let idx_drop = sea_query::Index::drop()
339                .table(Alias::new(table))
340                .name(&index_name)
341                .to_owned();
342            Ok(vec![BuiltQuery::DropIndex(Box::new(idx_drop))])
343        }
344        TableConstraint::Check { name, .. } => {
345            // SQLite does not support ALTER TABLE ... DROP CONSTRAINT CHECK
346            if *backend == DatabaseBackend::Sqlite {
347                // Use temporary table approach for SQLite
348                let table_def = current_schema.iter().find(|t| t.name == table).ok_or_else(|| QueryError::Other(format!("Table '{}' not found in current schema. SQLite requires current schema information to remove constraints.", table)))?;
349
350                // Create new constraints without the removed check constraint
351                let mut new_constraints = table_def.constraints.clone();
352                new_constraints.retain(|c| match (c, constraint) {
353                    (
354                        TableConstraint::Check { name: c_name, .. },
355                        TableConstraint::Check { name: r_name, .. },
356                    ) => c_name != r_name,
357                    _ => true,
358                });
359
360                // Generate temporary table name
361                let temp_table = format!("{}_temp", table);
362
363                // 1. Create temporary table without the removed constraint
364                let create_temp_table = build_create_table_for_backend(
365                    backend,
366                    &temp_table,
367                    &table_def.columns,
368                    &new_constraints,
369                );
370                let create_query = BuiltQuery::CreateTable(Box::new(create_temp_table));
371
372                // 2. Copy data (all columns)
373                let column_aliases: Vec<Alias> = table_def
374                    .columns
375                    .iter()
376                    .map(|c| Alias::new(&c.name))
377                    .collect();
378                let mut select_query = Query::select();
379                for col_alias in &column_aliases {
380                    select_query = select_query.column(col_alias.clone()).to_owned();
381                }
382                select_query = select_query.from(Alias::new(table)).to_owned();
383
384                let insert_stmt = Query::insert()
385                    .into_table(Alias::new(&temp_table))
386                    .columns(column_aliases.clone())
387                    .select_from(select_query)
388                    .unwrap()
389                    .to_owned();
390                let insert_query = BuiltQuery::Insert(Box::new(insert_stmt));
391
392                // 3. Drop original table
393                let drop_table = Table::drop().table(Alias::new(table)).to_owned();
394                let drop_query = BuiltQuery::DropTable(Box::new(drop_table));
395
396                // 4. Rename temporary table to original name
397                let rename_query = build_rename_table(&temp_table, table);
398
399                // 5. Recreate indexes from Index constraints
400                let mut index_queries = Vec::new();
401                for c in &table_def.constraints {
402                    if let TableConstraint::Index {
403                        name: idx_name,
404                        columns: idx_cols,
405                    } = c
406                    {
407                        let index_name = vespertide_naming::build_index_name(
408                            table,
409                            idx_cols,
410                            idx_name.as_deref(),
411                        );
412                        let mut idx_stmt = sea_query::Index::create();
413                        idx_stmt = idx_stmt.name(&index_name).to_owned();
414                        for col_name in idx_cols {
415                            idx_stmt = idx_stmt.col(Alias::new(col_name)).to_owned();
416                        }
417                        idx_stmt = idx_stmt.table(Alias::new(table)).to_owned();
418                        index_queries.push(BuiltQuery::CreateIndex(Box::new(idx_stmt)));
419                    }
420                }
421
422                let mut queries = vec![create_query, insert_query, drop_query, rename_query];
423                queries.extend(index_queries);
424                Ok(queries)
425            } else {
426                let pg_sql = format!("ALTER TABLE \"{}\" DROP CONSTRAINT \"{}\"", table, name);
427                let mysql_sql = format!("ALTER TABLE `{}` DROP CHECK `{}`", table, name);
428                Ok(vec![BuiltQuery::Raw(RawSql::per_backend(
429                    pg_sql.clone(),
430                    mysql_sql,
431                    pg_sql,
432                ))])
433            }
434        }
435    }
436}
437
438#[cfg(test)]
439mod tests {
440    use super::*;
441    use crate::sql::types::DatabaseBackend;
442    use insta::{assert_snapshot, with_settings};
443    use rstest::rstest;
444    use vespertide_core::{ColumnDef, ColumnType, SimpleColumnType, TableConstraint, TableDef};
445
446    #[rstest]
447    #[case::remove_constraint_primary_key_postgres(
448        "remove_constraint_primary_key_postgres",
449        DatabaseBackend::Postgres,
450        &["DROP CONSTRAINT \"users_pkey\""]
451    )]
452    #[case::remove_constraint_primary_key_mysql(
453        "remove_constraint_primary_key_mysql",
454        DatabaseBackend::MySql,
455        &["DROP PRIMARY KEY"]
456    )]
457    #[case::remove_constraint_primary_key_sqlite(
458        "remove_constraint_primary_key_sqlite",
459        DatabaseBackend::Sqlite,
460        &["CREATE TABLE \"users_temp\""]
461    )]
462    #[case::remove_constraint_unique_named_postgres(
463        "remove_constraint_unique_named_postgres",
464        DatabaseBackend::Postgres,
465        &["DROP INDEX \"uq_users__uq_email\""]
466    )]
467    #[case::remove_constraint_unique_named_mysql(
468        "remove_constraint_unique_named_mysql",
469        DatabaseBackend::MySql,
470        &["DROP INDEX `uq_users__uq_email`"]
471    )]
472    #[case::remove_constraint_unique_named_sqlite(
473        "remove_constraint_unique_named_sqlite",
474        DatabaseBackend::Sqlite,
475        &["CREATE TABLE \"users_temp\""]
476    )]
477    #[case::remove_constraint_foreign_key_named_postgres(
478        "remove_constraint_foreign_key_named_postgres",
479        DatabaseBackend::Postgres,
480        &["DROP CONSTRAINT \"fk_users__fk_user\""]
481    )]
482    #[case::remove_constraint_foreign_key_named_mysql(
483        "remove_constraint_foreign_key_named_mysql",
484        DatabaseBackend::MySql,
485        &["DROP FOREIGN KEY `fk_users__fk_user`"]
486    )]
487    #[case::remove_constraint_foreign_key_named_sqlite(
488        "remove_constraint_foreign_key_named_sqlite",
489        DatabaseBackend::Sqlite,
490        &["CREATE TABLE \"users_temp\""]
491    )]
492    #[case::remove_constraint_check_named_postgres(
493        "remove_constraint_check_named_postgres",
494        DatabaseBackend::Postgres,
495        &["DROP CONSTRAINT \"chk_age\""]
496    )]
497    #[case::remove_constraint_check_named_mysql(
498        "remove_constraint_check_named_mysql",
499        DatabaseBackend::MySql,
500        &["DROP CHECK `chk_age`"]
501    )]
502    #[case::remove_constraint_check_named_sqlite(
503        "remove_constraint_check_named_sqlite",
504        DatabaseBackend::Sqlite,
505        &["CREATE TABLE \"users_temp\""]
506    )]
507    fn test_remove_constraint(
508        #[case] title: &str,
509        #[case] backend: DatabaseBackend,
510        #[case] expected: &[&str],
511    ) {
512        let constraint = if title.contains("primary_key") {
513            TableConstraint::PrimaryKey {
514                columns: vec!["id".into()],
515                auto_increment: false,
516            }
517        } else if title.contains("unique") {
518            TableConstraint::Unique {
519                name: Some("uq_email".into()),
520                columns: vec!["email".into()],
521            }
522        } else if title.contains("foreign_key") {
523            TableConstraint::ForeignKey {
524                name: Some("fk_user".into()),
525                columns: vec!["user_id".into()],
526                ref_table: "users".into(),
527                ref_columns: vec!["id".into()],
528                on_delete: None,
529                on_update: None,
530            }
531        } else {
532            TableConstraint::Check {
533                name: "chk_age".into(),
534                expr: "age > 0".into(),
535            }
536        };
537
538        // For SQLite, we need to provide current schema with the constraint to be removed
539        let current_schema = vec![TableDef {
540            name: "users".into(),
541            description: None,
542            columns: if title.contains("check") {
543                vec![
544                    ColumnDef {
545                        name: "id".into(),
546                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
547                        nullable: false,
548                        default: None,
549                        comment: None,
550                        primary_key: None,
551                        unique: None,
552                        index: None,
553                        foreign_key: None,
554                    },
555                    ColumnDef {
556                        name: "age".into(),
557                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
558                        nullable: true,
559                        default: None,
560                        comment: None,
561                        primary_key: None,
562                        unique: None,
563                        index: None,
564                        foreign_key: None,
565                    },
566                ]
567            } else if title.contains("foreign_key") {
568                vec![
569                    ColumnDef {
570                        name: "id".into(),
571                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
572                        nullable: false,
573                        default: None,
574                        comment: None,
575                        primary_key: None,
576                        unique: None,
577                        index: None,
578                        foreign_key: None,
579                    },
580                    ColumnDef {
581                        name: "user_id".into(),
582                        r#type: ColumnType::Simple(SimpleColumnType::Integer),
583                        nullable: true,
584                        default: None,
585                        comment: None,
586                        primary_key: None,
587                        unique: None,
588                        index: None,
589                        foreign_key: None,
590                    },
591                ]
592            } else {
593                // primary key / unique cases
594                vec![ColumnDef {
595                    name: "id".into(),
596                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
597                    nullable: false,
598                    default: None,
599                    comment: None,
600                    primary_key: None,
601                    unique: None,
602                    index: None,
603                    foreign_key: None,
604                }]
605            },
606            constraints: vec![constraint.clone()],
607        }];
608
609        let result =
610            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
611        let sql = result[0].build(backend);
612        for exp in expected {
613            assert!(
614                sql.contains(exp),
615                "Expected SQL to contain '{}', got: {}",
616                exp,
617                sql
618            );
619        }
620
621        with_settings!({ snapshot_suffix => format!("remove_constraint_{}", title) }, {
622            assert_snapshot!(result.iter().map(|q| q.build(backend)).collect::<Vec<String>>().join("\n"));
623        });
624    }
625
626    #[test]
627    fn test_remove_constraint_primary_key_sqlite_table_not_found() {
628        // Test error when table is not found (line 25)
629        let constraint = TableConstraint::PrimaryKey {
630            columns: vec!["id".into()],
631            auto_increment: false,
632        };
633        let result = build_remove_constraint(
634            &DatabaseBackend::Sqlite,
635            "nonexistent_table",
636            &constraint,
637            &[], // Empty schema
638        );
639        assert!(result.is_err());
640        let err_msg = result.unwrap_err().to_string();
641        assert!(err_msg.contains("Table 'nonexistent_table' not found in current schema"));
642    }
643
644    #[rstest]
645    #[case::remove_primary_key_with_index_postgres(DatabaseBackend::Postgres)]
646    #[case::remove_primary_key_with_index_mysql(DatabaseBackend::MySql)]
647    #[case::remove_primary_key_with_index_sqlite(DatabaseBackend::Sqlite)]
648    fn test_remove_constraint_primary_key_with_index(#[case] backend: DatabaseBackend) {
649        // Test PrimaryKey removal with indexes
650        let constraint = TableConstraint::PrimaryKey {
651            columns: vec!["id".into()],
652            auto_increment: false,
653        };
654        let current_schema = vec![TableDef {
655            name: "users".into(),
656            description: None,
657            columns: vec![ColumnDef {
658                name: "id".into(),
659                r#type: ColumnType::Simple(SimpleColumnType::Integer),
660                nullable: false,
661                default: None,
662                comment: None,
663                primary_key: None,
664                unique: None,
665                index: None,
666                foreign_key: None,
667            }],
668            constraints: vec![
669                constraint.clone(),
670                TableConstraint::Index {
671                    name: Some("idx_id".into()),
672                    columns: vec!["id".into()],
673                },
674            ],
675        }];
676
677        let result =
678            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
679        let sql = result
680            .iter()
681            .map(|q| q.build(backend))
682            .collect::<Vec<String>>()
683            .join("\n");
684
685        if matches!(backend, DatabaseBackend::Sqlite) {
686            assert!(sql.contains("CREATE INDEX"));
687            assert!(sql.contains("ix_users__idx_id"));
688        }
689
690        with_settings!({ snapshot_suffix => format!("remove_primary_key_with_index_{:?}", backend) }, {
691            assert_snapshot!(sql);
692        });
693    }
694
695    #[rstest]
696    #[case::remove_primary_key_with_unique_constraint_postgres(DatabaseBackend::Postgres)]
697    #[case::remove_primary_key_with_unique_constraint_mysql(DatabaseBackend::MySql)]
698    #[case::remove_primary_key_with_unique_constraint_sqlite(DatabaseBackend::Sqlite)]
699    fn test_remove_constraint_primary_key_with_unique_constraint(#[case] backend: DatabaseBackend) {
700        // Test PrimaryKey removal with unique constraint
701        let constraint = TableConstraint::PrimaryKey {
702            columns: vec!["id".into()],
703            auto_increment: false,
704        };
705        let current_schema = vec![TableDef {
706            name: "users".into(),
707            description: None,
708            columns: vec![ColumnDef {
709                name: "id".into(),
710                r#type: ColumnType::Simple(SimpleColumnType::Integer),
711                nullable: false,
712                default: None,
713                comment: None,
714                primary_key: None,
715                unique: None,
716                index: None,
717                foreign_key: None,
718            }],
719            constraints: vec![
720                constraint.clone(),
721                TableConstraint::Unique {
722                    name: Some("uq_email".into()),
723                    columns: vec!["email".into()],
724                },
725            ],
726        }];
727
728        let result =
729            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
730        let sql = result
731            .iter()
732            .map(|q| q.build(backend))
733            .collect::<Vec<String>>()
734            .join("\n");
735
736        if matches!(backend, DatabaseBackend::Sqlite) {
737            // Unique constraint should be in the temp table definition
738            assert!(sql.contains("CREATE TABLE"));
739        }
740
741        with_settings!({ snapshot_suffix => format!("remove_primary_key_with_unique_constraint_{:?}", backend) }, {
742            assert_snapshot!(sql);
743        });
744    }
745
746    #[test]
747    fn test_remove_constraint_unique_sqlite_table_not_found() {
748        // Test error when table is not found (line 112)
749        let constraint = TableConstraint::Unique {
750            name: Some("uq_email".into()),
751            columns: vec!["email".into()],
752        };
753        let result = build_remove_constraint(
754            &DatabaseBackend::Sqlite,
755            "nonexistent_table",
756            &constraint,
757            &[], // Empty schema
758        );
759        assert!(result.is_err());
760        let err_msg = result.unwrap_err().to_string();
761        assert!(err_msg.contains("Table 'nonexistent_table' not found in current schema"));
762    }
763
764    #[rstest]
765    #[case::remove_unique_without_name_postgres(DatabaseBackend::Postgres)]
766    #[case::remove_unique_without_name_mysql(DatabaseBackend::MySql)]
767    #[case::remove_unique_without_name_sqlite(DatabaseBackend::Sqlite)]
768    fn test_remove_constraint_unique_without_name(#[case] backend: DatabaseBackend) {
769        // Test Unique removal without name (lines 134, 137, 210)
770        let constraint = TableConstraint::Unique {
771            name: None,
772            columns: vec!["email".into()],
773        };
774        let current_schema = vec![TableDef {
775            name: "users".into(),
776            description: None,
777            columns: vec![
778                ColumnDef {
779                    name: "id".into(),
780                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
781                    nullable: false,
782                    default: None,
783                    comment: None,
784                    primary_key: None,
785                    unique: None,
786                    index: None,
787                    foreign_key: None,
788                },
789                ColumnDef {
790                    name: "email".into(),
791                    r#type: ColumnType::Simple(SimpleColumnType::Text),
792                    nullable: true,
793                    default: None,
794                    comment: None,
795                    primary_key: None,
796                    unique: None,
797                    index: None,
798                    foreign_key: None,
799                },
800            ],
801            constraints: vec![constraint.clone()],
802        }];
803
804        let result =
805            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
806        let sql = result
807            .iter()
808            .map(|q| q.build(backend))
809            .collect::<Vec<String>>()
810            .join("\n");
811
812        // Should generate default constraint name
813        if !matches!(backend, DatabaseBackend::Sqlite) {
814            assert!(sql.contains("users_email_key") || sql.contains("email"));
815        }
816
817        with_settings!({ snapshot_suffix => format!("remove_unique_without_name_{:?}", backend) }, {
818            assert_snapshot!(sql);
819        });
820    }
821
822    #[rstest]
823    #[case::remove_unique_with_index_postgres(DatabaseBackend::Postgres)]
824    #[case::remove_unique_with_index_mysql(DatabaseBackend::MySql)]
825    #[case::remove_unique_with_index_sqlite(DatabaseBackend::Sqlite)]
826    fn test_remove_constraint_unique_with_index(#[case] backend: DatabaseBackend) {
827        // Test Unique removal with indexes
828        let constraint = TableConstraint::Unique {
829            name: Some("uq_email".into()),
830            columns: vec!["email".into()],
831        };
832        let current_schema = vec![TableDef {
833            name: "users".into(),
834            description: None,
835            columns: vec![
836                ColumnDef {
837                    name: "id".into(),
838                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
839                    nullable: false,
840                    default: None,
841                    comment: None,
842                    primary_key: None,
843                    unique: None,
844                    index: None,
845                    foreign_key: None,
846                },
847                ColumnDef {
848                    name: "email".into(),
849                    r#type: ColumnType::Simple(SimpleColumnType::Text),
850                    nullable: true,
851                    default: None,
852                    comment: None,
853                    primary_key: None,
854                    unique: None,
855                    index: None,
856                    foreign_key: None,
857                },
858            ],
859            constraints: vec![
860                constraint.clone(),
861                TableConstraint::Index {
862                    name: Some("idx_id".into()),
863                    columns: vec!["id".into()],
864                },
865            ],
866        }];
867
868        let result =
869            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
870        let sql = result
871            .iter()
872            .map(|q| q.build(backend))
873            .collect::<Vec<String>>()
874            .join("\n");
875
876        if matches!(backend, DatabaseBackend::Sqlite) {
877            assert!(sql.contains("CREATE INDEX"));
878            assert!(sql.contains("ix_users__idx_id"));
879        }
880
881        with_settings!({ snapshot_suffix => format!("remove_unique_with_index_{:?}", backend) }, {
882            assert_snapshot!(sql);
883        });
884    }
885
886    #[rstest]
887    #[case::remove_unique_with_other_unique_constraint_postgres(DatabaseBackend::Postgres)]
888    #[case::remove_unique_with_other_unique_constraint_mysql(DatabaseBackend::MySql)]
889    #[case::remove_unique_with_other_unique_constraint_sqlite(DatabaseBackend::Sqlite)]
890    fn test_remove_constraint_unique_with_other_unique_constraint(
891        #[case] backend: DatabaseBackend,
892    ) {
893        // Test Unique removal with another unique constraint
894        let constraint = TableConstraint::Unique {
895            name: Some("uq_email".into()),
896            columns: vec!["email".into()],
897        };
898        let current_schema = vec![TableDef {
899            name: "users".into(),
900            description: None,
901            columns: vec![
902                ColumnDef {
903                    name: "id".into(),
904                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
905                    nullable: false,
906                    default: None,
907                    comment: None,
908                    primary_key: None,
909                    unique: None,
910                    index: None,
911                    foreign_key: None,
912                },
913                ColumnDef {
914                    name: "email".into(),
915                    r#type: ColumnType::Simple(SimpleColumnType::Text),
916                    nullable: true,
917                    default: None,
918                    comment: None,
919                    primary_key: None,
920                    unique: None,
921                    index: None,
922                    foreign_key: None,
923                },
924            ],
925            constraints: vec![
926                constraint.clone(),
927                TableConstraint::Unique {
928                    name: Some("uq_name".into()),
929                    columns: vec!["name".into()],
930                },
931            ],
932        }];
933
934        let result =
935            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
936        let sql = result
937            .iter()
938            .map(|q| q.build(backend))
939            .collect::<Vec<String>>()
940            .join("\n");
941
942        if matches!(backend, DatabaseBackend::Sqlite) {
943            // The remaining unique constraint should be preserved
944            assert!(sql.contains("CREATE TABLE"));
945        }
946
947        with_settings!({ snapshot_suffix => format!("remove_unique_with_other_unique_constraint_{:?}", backend) }, {
948            assert_snapshot!(sql);
949        });
950    }
951
952    #[test]
953    fn test_remove_constraint_foreign_key_sqlite_table_not_found() {
954        // Test error when table is not found (line 236)
955        let constraint = TableConstraint::ForeignKey {
956            name: Some("fk_user".into()),
957            columns: vec!["user_id".into()],
958            ref_table: "users".into(),
959            ref_columns: vec!["id".into()],
960            on_delete: None,
961            on_update: None,
962        };
963        let result = build_remove_constraint(
964            &DatabaseBackend::Sqlite,
965            "nonexistent_table",
966            &constraint,
967            &[], // Empty schema
968        );
969        assert!(result.is_err());
970        let err_msg = result.unwrap_err().to_string();
971        assert!(err_msg.contains("Table 'nonexistent_table' not found in current schema"));
972    }
973
974    #[rstest]
975    #[case::remove_foreign_key_without_name_postgres(DatabaseBackend::Postgres)]
976    #[case::remove_foreign_key_without_name_mysql(DatabaseBackend::MySql)]
977    #[case::remove_foreign_key_without_name_sqlite(DatabaseBackend::Sqlite)]
978    fn test_remove_constraint_foreign_key_without_name(#[case] backend: DatabaseBackend) {
979        // Test ForeignKey removal without name (lines 260, 263, 329)
980        let constraint = TableConstraint::ForeignKey {
981            name: None,
982            columns: vec!["user_id".into()],
983            ref_table: "users".into(),
984            ref_columns: vec!["id".into()],
985            on_delete: None,
986            on_update: None,
987        };
988        let current_schema = vec![TableDef {
989            name: "posts".into(),
990            description: None,
991            columns: vec![
992                ColumnDef {
993                    name: "id".into(),
994                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
995                    nullable: false,
996                    default: None,
997                    comment: None,
998                    primary_key: None,
999                    unique: None,
1000                    index: None,
1001                    foreign_key: None,
1002                },
1003                ColumnDef {
1004                    name: "user_id".into(),
1005                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1006                    nullable: true,
1007                    default: None,
1008                    comment: None,
1009                    primary_key: None,
1010                    unique: None,
1011                    index: None,
1012                    foreign_key: None,
1013                },
1014            ],
1015            constraints: vec![constraint.clone()],
1016        }];
1017
1018        let result =
1019            build_remove_constraint(&backend, "posts", &constraint, &current_schema).unwrap();
1020        let sql = result
1021            .iter()
1022            .map(|q| q.build(backend))
1023            .collect::<Vec<String>>()
1024            .join("\n");
1025
1026        // Should generate default constraint name
1027        if !matches!(backend, DatabaseBackend::Sqlite) {
1028            assert!(sql.contains("posts_user_id_fkey") || sql.contains("user_id"));
1029        }
1030
1031        with_settings!({ snapshot_suffix => format!("remove_foreign_key_without_name_{:?}", backend) }, {
1032            assert_snapshot!(sql);
1033        });
1034    }
1035
1036    #[rstest]
1037    #[case::remove_foreign_key_with_index_postgres(DatabaseBackend::Postgres)]
1038    #[case::remove_foreign_key_with_index_mysql(DatabaseBackend::MySql)]
1039    #[case::remove_foreign_key_with_index_sqlite(DatabaseBackend::Sqlite)]
1040    fn test_remove_constraint_foreign_key_with_index(#[case] backend: DatabaseBackend) {
1041        // Test ForeignKey removal with indexes
1042        let constraint = TableConstraint::ForeignKey {
1043            name: Some("fk_user".into()),
1044            columns: vec!["user_id".into()],
1045            ref_table: "users".into(),
1046            ref_columns: vec!["id".into()],
1047            on_delete: None,
1048            on_update: None,
1049        };
1050        let current_schema = vec![TableDef {
1051            name: "posts".into(),
1052            description: None,
1053            columns: vec![
1054                ColumnDef {
1055                    name: "id".into(),
1056                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1057                    nullable: false,
1058                    default: None,
1059                    comment: None,
1060                    primary_key: None,
1061                    unique: None,
1062                    index: None,
1063                    foreign_key: None,
1064                },
1065                ColumnDef {
1066                    name: "user_id".into(),
1067                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1068                    nullable: true,
1069                    default: None,
1070                    comment: None,
1071                    primary_key: None,
1072                    unique: None,
1073                    index: None,
1074                    foreign_key: None,
1075                },
1076            ],
1077            constraints: vec![
1078                constraint.clone(),
1079                TableConstraint::Index {
1080                    name: Some("idx_user_id".into()),
1081                    columns: vec!["user_id".into()],
1082                },
1083            ],
1084        }];
1085
1086        let result =
1087            build_remove_constraint(&backend, "posts", &constraint, &current_schema).unwrap();
1088        let sql = result
1089            .iter()
1090            .map(|q| q.build(backend))
1091            .collect::<Vec<String>>()
1092            .join("\n");
1093
1094        if matches!(backend, DatabaseBackend::Sqlite) {
1095            assert!(sql.contains("CREATE INDEX"));
1096            assert!(sql.contains("idx_user_id"));
1097        }
1098
1099        with_settings!({ snapshot_suffix => format!("remove_foreign_key_with_index_{:?}", backend) }, {
1100            assert_snapshot!(sql);
1101        });
1102    }
1103
1104    #[rstest]
1105    #[case::remove_foreign_key_with_unique_constraint_postgres(DatabaseBackend::Postgres)]
1106    #[case::remove_foreign_key_with_unique_constraint_mysql(DatabaseBackend::MySql)]
1107    #[case::remove_foreign_key_with_unique_constraint_sqlite(DatabaseBackend::Sqlite)]
1108    fn test_remove_constraint_foreign_key_with_unique_constraint(#[case] backend: DatabaseBackend) {
1109        // Test ForeignKey removal with unique constraint
1110        let constraint = TableConstraint::ForeignKey {
1111            name: Some("fk_user".into()),
1112            columns: vec!["user_id".into()],
1113            ref_table: "users".into(),
1114            ref_columns: vec!["id".into()],
1115            on_delete: None,
1116            on_update: None,
1117        };
1118        let current_schema = vec![TableDef {
1119            name: "posts".into(),
1120            description: None,
1121            columns: vec![
1122                ColumnDef {
1123                    name: "id".into(),
1124                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1125                    nullable: false,
1126                    default: None,
1127                    comment: None,
1128                    primary_key: None,
1129                    unique: None,
1130                    index: None,
1131                    foreign_key: None,
1132                },
1133                ColumnDef {
1134                    name: "user_id".into(),
1135                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1136                    nullable: true,
1137                    default: None,
1138                    comment: None,
1139                    primary_key: None,
1140                    unique: None,
1141                    index: None,
1142                    foreign_key: None,
1143                },
1144            ],
1145            constraints: vec![
1146                constraint.clone(),
1147                TableConstraint::Unique {
1148                    name: Some("uq_user_id".into()),
1149                    columns: vec!["user_id".into()],
1150                },
1151            ],
1152        }];
1153
1154        let result =
1155            build_remove_constraint(&backend, "posts", &constraint, &current_schema).unwrap();
1156        let sql = result
1157            .iter()
1158            .map(|q| q.build(backend))
1159            .collect::<Vec<String>>()
1160            .join("\n");
1161
1162        if matches!(backend, DatabaseBackend::Sqlite) {
1163            // Unique constraint should be preserved in the temp table
1164            assert!(sql.contains("CREATE TABLE"));
1165        }
1166
1167        with_settings!({ snapshot_suffix => format!("remove_foreign_key_with_unique_constraint_{:?}", backend) }, {
1168            assert_snapshot!(sql);
1169        });
1170    }
1171
1172    #[test]
1173    fn test_remove_constraint_check_sqlite_table_not_found() {
1174        // Test error when table is not found (line 346)
1175        let constraint = TableConstraint::Check {
1176            name: "chk_age".into(),
1177            expr: "age > 0".into(),
1178        };
1179        let result = build_remove_constraint(
1180            &DatabaseBackend::Sqlite,
1181            "nonexistent_table",
1182            &constraint,
1183            &[], // Empty schema
1184        );
1185        assert!(result.is_err());
1186        let err_msg = result.unwrap_err().to_string();
1187        assert!(err_msg.contains("Table 'nonexistent_table' not found in current schema"));
1188    }
1189
1190    #[rstest]
1191    #[case::remove_check_with_index_postgres(DatabaseBackend::Postgres)]
1192    #[case::remove_check_with_index_mysql(DatabaseBackend::MySql)]
1193    #[case::remove_check_with_index_sqlite(DatabaseBackend::Sqlite)]
1194    fn test_remove_constraint_check_with_index(#[case] backend: DatabaseBackend) {
1195        // Test Check removal with indexes
1196        let constraint = TableConstraint::Check {
1197            name: "chk_age".into(),
1198            expr: "age > 0".into(),
1199        };
1200        let current_schema = vec![TableDef {
1201            name: "users".into(),
1202            description: None,
1203            columns: vec![
1204                ColumnDef {
1205                    name: "id".into(),
1206                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1207                    nullable: false,
1208                    default: None,
1209                    comment: None,
1210                    primary_key: None,
1211                    unique: None,
1212                    index: None,
1213                    foreign_key: None,
1214                },
1215                ColumnDef {
1216                    name: "age".into(),
1217                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1218                    nullable: true,
1219                    default: None,
1220                    comment: None,
1221                    primary_key: None,
1222                    unique: None,
1223                    index: None,
1224                    foreign_key: None,
1225                },
1226            ],
1227            constraints: vec![
1228                constraint.clone(),
1229                TableConstraint::Index {
1230                    name: Some("idx_age".into()),
1231                    columns: vec!["age".into()],
1232                },
1233            ],
1234        }];
1235
1236        let result =
1237            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
1238        let sql = result
1239            .iter()
1240            .map(|q| q.build(backend))
1241            .collect::<Vec<String>>()
1242            .join("\n");
1243
1244        if matches!(backend, DatabaseBackend::Sqlite) {
1245            assert!(sql.contains("CREATE INDEX"));
1246            assert!(sql.contains("idx_age"));
1247        }
1248
1249        with_settings!({ snapshot_suffix => format!("remove_check_with_index_{:?}", backend) }, {
1250            assert_snapshot!(sql);
1251        });
1252    }
1253
1254    #[rstest]
1255    #[case::remove_check_with_unique_constraint_postgres(DatabaseBackend::Postgres)]
1256    #[case::remove_check_with_unique_constraint_mysql(DatabaseBackend::MySql)]
1257    #[case::remove_check_with_unique_constraint_sqlite(DatabaseBackend::Sqlite)]
1258    fn test_remove_constraint_check_with_unique_constraint(#[case] backend: DatabaseBackend) {
1259        // Test Check removal with unique constraint
1260        let constraint = TableConstraint::Check {
1261            name: "chk_age".into(),
1262            expr: "age > 0".into(),
1263        };
1264        let current_schema = vec![TableDef {
1265            name: "users".into(),
1266            description: None,
1267            columns: vec![
1268                ColumnDef {
1269                    name: "id".into(),
1270                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1271                    nullable: false,
1272                    default: None,
1273                    comment: None,
1274                    primary_key: None,
1275                    unique: None,
1276                    index: None,
1277                    foreign_key: None,
1278                },
1279                ColumnDef {
1280                    name: "age".into(),
1281                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1282                    nullable: true,
1283                    default: None,
1284                    comment: None,
1285                    primary_key: None,
1286                    unique: None,
1287                    index: None,
1288                    foreign_key: None,
1289                },
1290            ],
1291            constraints: vec![
1292                constraint.clone(),
1293                TableConstraint::Unique {
1294                    name: Some("uq_age".into()),
1295                    columns: vec!["age".into()],
1296                },
1297            ],
1298        }];
1299
1300        let result =
1301            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
1302        let sql = result
1303            .iter()
1304            .map(|q| q.build(backend))
1305            .collect::<Vec<String>>()
1306            .join("\n");
1307
1308        if matches!(backend, DatabaseBackend::Sqlite) {
1309            // Unique constraint should be preserved in the temp table
1310            assert!(sql.contains("CREATE TABLE"));
1311        }
1312
1313        with_settings!({ snapshot_suffix => format!("remove_check_with_unique_constraint_{:?}", backend) }, {
1314            assert_snapshot!(sql);
1315        });
1316    }
1317
1318    #[rstest]
1319    #[case::remove_unique_with_other_constraints_postgres(DatabaseBackend::Postgres)]
1320    #[case::remove_unique_with_other_constraints_mysql(DatabaseBackend::MySql)]
1321    #[case::remove_unique_with_other_constraints_sqlite(DatabaseBackend::Sqlite)]
1322    fn test_remove_constraint_unique_with_other_constraints(#[case] backend: DatabaseBackend) {
1323        // Test Unique removal with other constraint types (line 137)
1324        let constraint = TableConstraint::Unique {
1325            name: Some("uq_email".into()),
1326            columns: vec!["email".into()],
1327        };
1328        let current_schema = vec![TableDef {
1329            name: "users".into(),
1330            description: None,
1331            columns: vec![
1332                ColumnDef {
1333                    name: "id".into(),
1334                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1335                    nullable: false,
1336                    default: None,
1337                    comment: None,
1338                    primary_key: None,
1339                    unique: None,
1340                    index: None,
1341                    foreign_key: None,
1342                },
1343                ColumnDef {
1344                    name: "email".into(),
1345                    r#type: ColumnType::Simple(SimpleColumnType::Text),
1346                    nullable: true,
1347                    default: None,
1348                    comment: None,
1349                    primary_key: None,
1350                    unique: None,
1351                    index: None,
1352                    foreign_key: None,
1353                },
1354            ],
1355            constraints: vec![
1356                TableConstraint::PrimaryKey {
1357                    columns: vec!["id".into()],
1358                    auto_increment: false,
1359                },
1360                constraint.clone(),
1361                TableConstraint::Check {
1362                    name: "chk_email".into(),
1363                    expr: "email IS NOT NULL".into(),
1364                },
1365            ],
1366        }];
1367
1368        let result =
1369            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
1370        let sql = result
1371            .iter()
1372            .map(|q| q.build(backend))
1373            .collect::<Vec<String>>()
1374            .join("\n");
1375
1376        // Should still work with other constraint types present
1377        assert!(sql.contains("DROP") || sql.contains("CREATE TABLE"));
1378
1379        with_settings!({ snapshot_suffix => format!("remove_unique_with_other_constraints_{:?}", backend) }, {
1380            assert_snapshot!(sql);
1381        });
1382    }
1383
1384    #[rstest]
1385    #[case::remove_foreign_key_with_other_constraints_postgres(DatabaseBackend::Postgres)]
1386    #[case::remove_foreign_key_with_other_constraints_mysql(DatabaseBackend::MySql)]
1387    #[case::remove_foreign_key_with_other_constraints_sqlite(DatabaseBackend::Sqlite)]
1388    fn test_remove_constraint_foreign_key_with_other_constraints(#[case] backend: DatabaseBackend) {
1389        // Test ForeignKey removal with other constraint types (line 263)
1390        let constraint = TableConstraint::ForeignKey {
1391            name: Some("fk_user".into()),
1392            columns: vec!["user_id".into()],
1393            ref_table: "users".into(),
1394            ref_columns: vec!["id".into()],
1395            on_delete: None,
1396            on_update: None,
1397        };
1398        let current_schema = vec![TableDef {
1399            name: "posts".into(),
1400            description: None,
1401            columns: vec![
1402                ColumnDef {
1403                    name: "id".into(),
1404                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1405                    nullable: false,
1406                    default: None,
1407                    comment: None,
1408                    primary_key: None,
1409                    unique: None,
1410                    index: None,
1411                    foreign_key: None,
1412                },
1413                ColumnDef {
1414                    name: "user_id".into(),
1415                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1416                    nullable: true,
1417                    default: None,
1418                    comment: None,
1419                    primary_key: None,
1420                    unique: None,
1421                    index: None,
1422                    foreign_key: None,
1423                },
1424            ],
1425            constraints: vec![
1426                TableConstraint::PrimaryKey {
1427                    columns: vec!["id".into()],
1428                    auto_increment: false,
1429                },
1430                constraint.clone(),
1431                TableConstraint::Unique {
1432                    name: Some("uq_user_id".into()),
1433                    columns: vec!["user_id".into()],
1434                },
1435                TableConstraint::Check {
1436                    name: "chk_user_id".into(),
1437                    expr: "user_id > 0".into(),
1438                },
1439            ],
1440        }];
1441
1442        let result =
1443            build_remove_constraint(&backend, "posts", &constraint, &current_schema).unwrap();
1444        let sql = result
1445            .iter()
1446            .map(|q| q.build(backend))
1447            .collect::<Vec<String>>()
1448            .join("\n");
1449
1450        // Should still work with other constraint types present
1451        assert!(sql.contains("DROP") || sql.contains("CREATE TABLE"));
1452
1453        with_settings!({ snapshot_suffix => format!("remove_foreign_key_with_other_constraints_{:?}", backend) }, {
1454            assert_snapshot!(sql);
1455        });
1456    }
1457
1458    #[rstest]
1459    #[case::remove_check_with_other_constraints_postgres(DatabaseBackend::Postgres)]
1460    #[case::remove_check_with_other_constraints_mysql(DatabaseBackend::MySql)]
1461    #[case::remove_check_with_other_constraints_sqlite(DatabaseBackend::Sqlite)]
1462    fn test_remove_constraint_check_with_other_constraints(#[case] backend: DatabaseBackend) {
1463        // Test Check removal with other constraint types (line 357)
1464        let constraint = TableConstraint::Check {
1465            name: "chk_age".into(),
1466            expr: "age > 0".into(),
1467        };
1468        let current_schema = vec![TableDef {
1469            name: "users".into(),
1470            description: None,
1471            columns: vec![
1472                ColumnDef {
1473                    name: "id".into(),
1474                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1475                    nullable: false,
1476                    default: None,
1477                    comment: None,
1478                    primary_key: None,
1479                    unique: None,
1480                    index: None,
1481                    foreign_key: None,
1482                },
1483                ColumnDef {
1484                    name: "age".into(),
1485                    r#type: ColumnType::Simple(SimpleColumnType::Integer),
1486                    nullable: true,
1487                    default: None,
1488                    comment: None,
1489                    primary_key: None,
1490                    unique: None,
1491                    index: None,
1492                    foreign_key: None,
1493                },
1494            ],
1495            constraints: vec![
1496                TableConstraint::PrimaryKey {
1497                    columns: vec!["id".into()],
1498                    auto_increment: false,
1499                },
1500                TableConstraint::Unique {
1501                    name: Some("uq_age".into()),
1502                    columns: vec!["age".into()],
1503                },
1504                constraint.clone(),
1505            ],
1506        }];
1507
1508        let result =
1509            build_remove_constraint(&backend, "users", &constraint, &current_schema).unwrap();
1510        let sql = result
1511            .iter()
1512            .map(|q| q.build(backend))
1513            .collect::<Vec<String>>()
1514            .join("\n");
1515
1516        // Should still work with other constraint types present
1517        assert!(sql.contains("DROP") || sql.contains("CREATE TABLE"));
1518
1519        with_settings!({ snapshot_suffix => format!("remove_check_with_other_constraints_{:?}", backend) }, {
1520            assert_snapshot!(sql);
1521        });
1522    }
1523
1524    #[rstest]
1525    #[case::remove_index_with_custom_inline_name_postgres(DatabaseBackend::Postgres)]
1526    #[case::remove_index_with_custom_inline_name_mysql(DatabaseBackend::MySql)]
1527    #[case::remove_index_with_custom_inline_name_sqlite(DatabaseBackend::Sqlite)]
1528    fn test_remove_constraint_index_with_custom_inline_name(#[case] backend: DatabaseBackend) {
1529        // Test Index removal with a custom name from inline index field
1530        // This tests the scenario where index: "custom_idx_name" is used
1531        let constraint = TableConstraint::Index {
1532            name: Some("custom_idx_email".into()),
1533            columns: vec!["email".into()],
1534        };
1535
1536        let schema = vec![TableDef {
1537            name: "users".to_string(),
1538            description: None,
1539            columns: vec![ColumnDef {
1540                name: "email".to_string(),
1541                r#type: ColumnType::Simple(SimpleColumnType::Text),
1542                nullable: true,
1543                default: None,
1544                comment: None,
1545                primary_key: None,
1546                unique: None,
1547                index: Some(vespertide_core::StrOrBoolOrArray::Str(
1548                    "custom_idx_email".into(),
1549                )),
1550                foreign_key: None,
1551            }],
1552            constraints: vec![],
1553        }];
1554
1555        let result = build_remove_constraint(&backend, "users", &constraint, &schema);
1556        assert!(result.is_ok());
1557        let sql = result
1558            .unwrap()
1559            .iter()
1560            .map(|q| q.build(backend))
1561            .collect::<Vec<String>>()
1562            .join("\n");
1563
1564        // Should use the custom index name
1565        assert!(sql.contains("custom_idx_email"));
1566
1567        with_settings!({ snapshot_suffix => format!("remove_index_custom_name_{:?}", backend) }, {
1568            assert_snapshot!(sql);
1569        });
1570    }
1571}
vespertide_query/sql/remove_constraint.rs

vespertide_query/sql/
remove_constraint.rs
