SQLite Forum

RENAME COLUMN incomplete documentation
Login

RENAME COLUMN incomplete documentation

(1) By Jeff M (jeffrey) on 2020-04-03 05:45:01 [link] [source]

I believe the RENAME COLUMN documentation is incomplete.

Currently, it reads:

ALTER TABLE RENAME COLUMN
The RENAME COLUMN TO syntax changes the column-name of table table-name into new-column-name. The column name is changed both within the table definition itself and also within all indexes, triggers, and views that reference the column. If the column name change would result in a semantic ambiguity in a trigger or view, then the RENAME COLUMN fails with an error and no changes are applied.

Issues:

  1. RENAME COLUMN requires version 3.25 or later. This is only clear from reading the Release History for 2018-09-15 (3.25.0).

  2. The doc mentions effects on "indexes, triggers, and views," but nothing about what happens if the renamed column is used as a foreign key.

(2) By Jeff M (jeffrey) on 2020-04-22 06:51:49 in reply to 1 [link] [source]

No one has commented on the documentation issue, so let me specifically ask a question: If I use ALTER TABLE RENAME COLUMN, what effect does it have on any foreign keys that use the old name?

(3) By Keith Medcalf (kmedcalf) on 2020-04-22 07:43:36 in reply to 2 [link] [source]

THe documentation says "the column name is changed within the table definition". Foreign key definitions are part of the "table definition". Rather trivial to demonstrate.

sqlite> create table parent (id integer primary key, stuff1 text collate nocase unique);
sqlite> create table child (id integer primary key, pid integer not null references parent(id), stuff2);
sqlite> alter table parent rename column id to chickadee;
sqlite> .schema
CREATE TABLE parent (chickadee integer primary key, stuff1 text collate nocase unique);
CREATE TABLE child (id integer primary key, pid integer not null references parent(chickadee), stuff2);

(4) By jose isaias cabrera (jicman) on 2020-04-22 17:31:11 in reply to 3 [source]

Keith, I am so glad you're here...! Thanks.

josé