0

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?

Community
  • 1
  • 1
Rod Carroll
  • 13
  • 4

2 Answers2

3

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

CL.
  • 173,858
  • 17
  • 217
  • 259
0

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.

Declan_K
  • 6,726
  • 2
  • 19
  • 30
  • Thanks for the quick reply. While I am not specifically deleting a row, I am dropping and then recreating A (due to SQLite's lack of a DROP COLUMN feature), but this will not affect B? – Rod Carroll Aug 17 '13 at 14:50
  • If you are dropping the table, then you are removing records which will drive a cascading delete. You will need to copy the data in Table B into a new backup table before you drop and recreate table A. Then you will need to copy the data back into table B. – Declan_K Aug 17 '13 at 14:52