CODEWITHSUNDEEP
sqlsqliteddl
Rod Carroll
Rod Carroll

Reputation: 13

SQLite Drop Column and Cascading Delete

I have researched how to drop a column using SQLite. Due to the lack of a DROP COLUMN statement, I am considering using the following workaround:

Delete column from SQLite table

This involves moving all data into a temporary table, dropping the original, and then re-creating it.

If I have a cascading delete dependency on the table I want to modify, how can I prevent any dependent tables from losing data?

Ex.

CREATE TABLE A (
    id INTEGER PRIMARY KEY,
    name TEXT,
    dummy INTEGER
)

CREATE TABLE B (
    id INTEGER PRIMARY KEY,
    name TEXT,
    a_id INTEGER,
    FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE CASCADE
)

Let's say I want to remove Column "dummy" from Table A, but I don't want to affect any rows in Table B. Can this be done?

Upvotes: 0

Views: 854

Answers (2)

CL.
CL.

Reputation: 180280

Foreign key constraints can be disabled with a PRAGMA. Just execute PRAGMA foreign_keys = off before removing records.

Upvotes: 3

Declan_K
Declan_K

Reputation: 6826

Cascading Deletes are based on records (rows) not attributes (columns). Removing the dummy column will not cause any cascading deletes as you are not removing any records from the parent table.

Upvotes: 0

Related Questions