Two example Python programs are given here.Database connection to an SQLite database is established by calling connect() method of the sqlite3 module and passing the database file name as argument.The ALTER SQL statement can be executed through a cursor object obtained from a database connection object.Detailed knowhow of SQLite is required to understand how altering the name or schema of a table affects the existing indexes, triggers and constraints on the table.The ADD COLUMN clause of the ALTER statement helps in adding a new column to an existing table.Using the RENAME TO clause of the ALTER statement the name of the SQLite table can be changed.The SQL statement ALTER provided by SQLite can modify a number of aspects of a table schema.Otherwise, you need to follow the steps described above to rename a column. If you are using SQLite 3.25.0, you should upgrade it and use the new syntax.Use the ALTER TABLE RENAME COLUMN to rename a column in a table. ![]() If you query the Locations table, you will see that the column Address has been renamed to Street: SELECT * FROM Locations Code language: SQL (Structured Query Language) ( sql ) RENAME TO Locations Code language: SQL (Structured Query Language) ( sql )įinally, commit the transaction: COMMIT Code language: SQL (Structured Query Language) ( sql ) Third, copy data from the table Locations to LocationsTemp: INSERT INTO LocationsTemp(Street,City,State,Country)įROM Locations Code language: SQL (Structured Query Language) ( sql )įourth, drop the Locations table: DROP TABLE Locations Code language: SQL (Structured Query Language) ( sql )įifth, rename the table LocationsTemp to Locations: ALTER TABLE LocationsTemp Second, create a new table called LocationsTemp with the same structure as the Locations table except for the Address column: CREATE TABLE LocationsTemp(Ĭode language: SQL (Structured Query Language) ( sql ) Suppose that you want to the change the column Address to Street.įirst, start a new transaction: BEGIN TRANSACTION Code language: SQL (Structured Query Language) ( sql ) The following statement recreates the Locations table: DROP TABLE IF EXISTS Locations Īnd this INSERT statement inserts a new row into the Locations table: INSERT INTO Locations(Address,City,State,Country) ![]() Fifth, rename the new table to the original table.Third, copy data from the original table to the new table.Second, create a new table whose structure is the same as the original one except for the column that you want to rename.If you’re using the SQLite with the version lower than 3.25.0 and could not upgrade, then you should follow these steps to rename a column: SQLite did not support the ALTER TABLE RENAME COLUMN syntax before version 3.25.0. ) Code language: SQL (Structured Query Language) ( sql ) The old way to rename column schema Locations Code language: Shell Session ( shell ) Output: LocationId Street City State Countryġ 3960 North 1st Street San Jose CA USA Code language: Shell Session ( shell )įinally, show the schema of the Locations table. RENAME COLUMN Address TO Street Code language: SQL (Structured Query Language) ( sql )įourth, query data from the Locations table: SELECT * FROM Locations Code language: SQL (Structured Query Language) ( sql ) Third, rename the column Address to Street by using the ALTER TABLE RENAME COLUMN statement: ALTER TABLE Locations VALUES( '3960 North 1st Street', 'San Jose', 'CA', 'USA') Code language: SQL (Structured Query Language) ( sql ) Second, insert a new row into the Locations table by using the INSERT statement: INSERT INTO Locations(Address,City,State,Country) ) Code language: SQL (Structured Query Language) ( sql ) Let’s take an example of using the ALTER TABLE RENAME COLUMN statement.įirst, create a new table called Locations: CREATE TABLE Locations( Second, specify the name of the column that you want to rename after the RENAME COLUMN keywords and the new name after the TO keyword. ![]() ![]()
0 Comments
Leave a Reply. |