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

vespertide_query/sql/
remove_constraint.rs
