Update of "Drop-RenameTable and RenameColumn"
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID: 7c048675f80cd9e1a69f7a9f02ded1dcb19c2c85
Page Name:Drop-RenameTable and RenameColumn
Date: 2019-12-24 11:09:17
Original User: sandro
Parent: fc7558abb5a0deb61df19bb46d3fbb3f59b3feff
Content

Back to 5.0.0-doc main page



Introduction

All historical versions of SQLite3 have always presented several limitations affecting the implementation of the standard SQL statement ALTER TABLE: All this was inconsistent from the usual behavior of other SQL DBMSes, and was easily perceived by many developers and users as a nasty and unpleasant limitation.

The situation has radically changed since the released version 3.25.0 of 2018-09-15.
SQLite3 now fully supports both of the above forms of ALTER TABLE and this, longly awaited, good news means that SQLite3 now behaves in the same way as any other DBMS on the market.

Seen from the SpatiaLite perspective, problems arise that need to be addressed. Therefore any use of ALTER TABLE on any Spatial Table or Column will have no effect on any Administration TABLE, causing the creation of an invalid DB layout

To resolve this issue, a complete set of corresponding SQL functions have been introduced in version 5.0.0 to insure that any Administration tasks needed are done correctly.
Let's now see each single SQL function in full details.


Warning

Both RenameTable() and RenameColumn() strictly require SQLite3 version 3.25.0 (or greater).
If SpatiaLite is running against an earlier version both functions will always raise an Exception complaining about the mismatching library version.

DropTable()

SELECT DropTable('some_db', 'some_table');
----------------------
1

SELECT DropTable(NULL, 'inexistent_table');
----------------------
DropTable exception - not existing table [main.inexistent_table]

SELECT DropTable(NULL, 'inexistent_table', 1);
----------------------
0
Supported arguments with their interpretation:

Caveat

A similar function named DropGeoTable() was supported by previous versions of SpatiaLite.

DropGeoTable() is now DEPRECATED, and will remain to avoid breaking existing applications and scripts.
It is strongly recommended use DropTable() for any new development as full and more reliable replacement.


RenameTable()

SELECT RenameTable('some_db', 'old_table', 'new_table');
----------------------
1

SELECT RenameTable(NULL, 'old_table', 'existing_new_table');
----------------------
RenameTable exception - already existing table [main.existing_new_table]

SELECT RenameTable(NULL, 'old_table', 'existing_new_table', 1);
----------------------
0
Supported arguments with their interpretation:

Be aware

SQLite3 can never rename a View or VirtualTable.
If you absolutely need to change the name of any View or VirtualTable after it's creation, you must first drop it, and then recreate it with the new name.


RenameColumn()

SELECT RenameColumn('some_db', 'table_name', 'old_column', 'new_column');
----------------------
1

SELECT RenameColumn(NULL, 'table_name', 'old_column', 'existing_column');
----------------------
RenameColumn exception - column already defined [main.table_name] existing_column

SELECT RenameColumn(NULL, 'table_name', 'old_column', 'existing_column', 1);
----------------------
0
Supported arguments with their interpretation:

Be aware

SQLite3 can never rename Columns belonging to a View or VirtualTable.
If you absolutely need to change the name of any Columns in a View or VirtualTable after it's creation, you must first drop it, and then recreate it with the new Column name.



Back to 5.0.0-doc main page