Sqlite enum column check constraints are not preserved after adding foreign key to table

[Pecon]

Laravel Version

12.19.3

PHP Version

8.4.8

Database Driver & Version

sqlite3 3.37.2

Description

I have been setting up tests for my project to validate that backed enum types in my project are compatible with the enums generated for my database schemas, and in doing so I discovered inconsistent behavior with the schema generation. When creating the table initially, or when explicitly updating a table's column, an enum-type column will generate as a varchar type with a check constraint that restricts the values of the defined enum. If you later alter this table by adding a foreign key, the resulting sequence of commands that the migration uses to generate the updated table does not preserve the check constraint of the enum columns.

Steps To Reproduce

Using the sqlite database driver, create a table with an enum column.

By running the migration with --pretend, I can verify that it is created with the intended check constraint.

create table "example_table" ("id" integer primary key autoincrement not null, "type" varchar check ("type" in ('a', 'b', 'c')) not null)

Now I want to expand on this table by adding a foreign key to a second table, so I'll create a second table and then add the foreign key to the first example table.

When I run the migration with --pretend, I can see that a few steps occur to copy the existing example_table's contents to a new table that has the foreign key. It then drops the original table and renames the new copy to replace the prior table with one which has the foreign key.

create table "example_table" ("id" integer primary key autoincrement not null, "type" varchar check ("type" in ('a', 'b', 'c')) not null)
create table "other_example_table" ("id" integer primary key autoincrement not null)
select name, type, not "notnull" as "nullable", dflt_value as "default", pk as "primary", hidden as "extra" from pragma_table_xinfo('example_table', 'main') order by cid asc
select "sql" from "main".sqlite_master where type = 'table' and name = 'example_table'
select 'primary' as name, group_concat(col) as columns, 1 as "unique", 1 as "primary" from (select name as col from pragma_table_xinfo('example_table', 'main') where pk > 0 order by pk, cid) group by name union select name, group_concat(col) as columns, "unique", origin = 'pk' as "primary" from (select il.*, ii.name as col from pragma_index_list('example_table', 'main') il, pragma_index_info(il.name, 'main') ii order by il.seq, ii.seqno) group by name, "unique", "primary"
select group_concat("from") as columns, 'main' as foreign_schema, "table" as foreign_table, group_concat("to") as foreign_columns, on_update, on_delete from (select * from pragma_foreign_key_list('example_table', 'main') order by id desc, seq) group by id, "table", on_update, on_delete
pragma foreign_keys
create table "__temp__example_table" (, foreign key("id") references "other_example_table"("id"))
insert into "__temp__example_table" () select from "example_table"
drop table "example_table"
alter table "__temp__example_table" rename to "example_table"

Unfortunately, after running this, I can verify with sqlite3's .schema function that the resulting final table with the foreign key has now lost the check constraint that was originally on the enum column.

CREATE TABLE IF NOT EXISTS "example_table" ("id" integer primary key autoincrement not null, "type" varchar not null, foreign key("id") references "other_example_table"("id"));

[hafezdivandari]

Check constraints are not fully supported on Laravel (#46512). A workaround is to redeclare the enum column when adding the foreign key:

$table->enum('type', ...)->change();
$table->foreign(...);

PS: there are currently no easy way to inspect the check constraint of a table in SQLite (neccessary step to recreate the table when modifying it). The only way seems to be parsing the create statement of the table in sqlite_master.