Support drop of foreign key constraint

(1.1) By Emile Fugulin (sytten) on 2022-05-03 14:12:13 edited from 1.0 [source]

Hi!

I did a bit of searching around to see if that was discussed/planned and didn't find much.
I was wondering if allowing users to drop columns that have a foreign key could/will be allowed at some point?
If so what are the hurdles currently preventing if from working?
I am guessing there is a larger discussion to be had on dropping constraints, but I am mostly curious about foreign keys.

Thanks :)

EDIT: I changed the title to better reflect the direction of the discussion

(2) By Simon Slavin (slavin) on 2022-04-30 05:12:58 in reply to 1.0 [link] [source]

Have you read

https://sqlite.org/foreignkeys.html#fk_schemacommands

? If so, a FOREIGN KEY relates two columns: the parent column and the child column. Which one are you proposing to drop ? What are you uncertain about ?

(3) By anonymous on 2022-04-30 13:03:11 in reply to 2 [link] [source]

A FOREIGN KEY relates two lists of columns; one from each table. If each list has length N, usually, N=1, but N can be larger.

(4) By Emile Fugulin (sytten) on 2022-04-30 13:07:13 in reply to 2 [link] [source]

I read it, not sure it is relevant for a drop column.

In my mind you just drop the column refered to in the alter statement. I dont understand why the FK constraint cant be dropped at the same time as it cannot cause a FK violation to drop a constraint.
I would not want to drop the parent column, I dont think any database system works that way.

Sqlite could force the user to explicitly drop the constraint before dropping the column like it does for indexes. But again dropping a foreign key constraint should always be safe AFAIK.

(5) By anonymous on 2022-05-01 13:08:09 in reply to 4 [link] [source]

I've seen database systems where you can't remove a table, a column, an index, or anything else until everything that depends on it has been fixed.

The goal, I think, is to make sure that you do your homework, and understand all the dependencies -- not just the obvious ones -- and properly take them into account before removing the object.

Not taking them all into account easily leads to some nasty problems, which (in larger databases) might affect tens of thousands of users. So the precaution is understandable.

(6) By Emile Fugulin (sytten) on 2022-05-01 17:11:54 in reply to 5 [link] [source]

Fair enough, then a DROP constraint to remove the foreign key would do the trick then the column could be dropped.

It is already possible by editing the schema directly and it is even proposed in the documentation, I just think it would be better to have a safe way to do it.

(7) By Emile Fugulin (sytten) on 2022-05-03 14:08:50 in reply to 1.0 [link] [source]

So I was able to remove the constraint by modifying the schema directly with the method specified in the documentation.

I think it would be interesting to start the discussion on a syntax to do that in a safe way. For inspiration here is what other databases do:

Postgres/Orable/Sql Server: ALTER TABLE table_name DROP CONSTRAINT fk_table_name_column_name
MySql: ALTER TABLE table_name DROP FOREIGN KEY table_name_ibfk_1

If the constraint is named then obviously the name must be used to drop it. Since sqlite doesn't create a name for a foreign key constraint if none is given, there either needs to be a convention for referencing an unnamed constraint or a new syntax.

As a thought experiment here is a proposal:

ALTER TABLE table_name DROP FOREIGN KEY column_1
ALTER TABLE table_name DROP FOREIGN KEY (column_1, column_2)
ALTER TABLE table_name DROP CONSTRAINT named_constraint

The DROP CONSTRAINT would only accept foreign key constraint to start with.