vespertide_query/sql/
remove_constraint.rs

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

vespertide_query/sql/
remove_constraint.rs
