Structure Tab

Within the Structure tab you can view the properties and CREATE statement for the selected database object. For a SQLite table, changes to the table's structure can also be made.

SQLite Manager Tab Reference Pages
Structure Browse & Search Execute SQL DB Settings Import Export On-Connect SQL

Making Changes to a Table's Structure

The SQLite Database engine provides only limited ALTER TABLE support which allows only the renaming of tables and the addition of new columns. Other operations such as editing or dropping columns must be done by renaming the original table, creating a new table with the old tables name and importing the records from the old table.

SQLite Manager does this all for you by creating the SQL statements and running them in the background. However this is not without risk. Before attempting to ALTER the structure of any table that contains valuable data, make a back up copy of that table first.

Add a Key Column to an Existing Table

Ideally any necessary primary or foreign keys should have been included when a table was created. That being said, it is possible at the present time, to use SQLite Manager to "shoehorn" a primary key and or foreign key where none exists into a SQLite table by the following methods.

Primary Key to be a Single Column that already exists in the table.

  1. In the Columns text box, right click on the column name to bring up the context menu and select Edit Column .
  2. Within the Column Type box , append PRIMARY KEY after data type.
  3. Click on the Change button and then click the Yes button on the Dangerous Operation dialog box.

For this to succeed, the PRIMARY KEY column must not contain duplicate values.

Column Name Column Type Default Value
Old Values Email TEXT
New Values Email TEXT PRIMARY KEY
Cancel Change

Primary Key to be a Single Column to be Added to a Table

  1. Enter the name and datatype of the new column
  3. Click the Add Column button and click Yes on Confirm the Operation
  4. In the Columns text box, right click on the new column name to bring up the context menu and select Edit Column .
  5. Within the Column Type box , append PRIMARY KEY after data type.
  6. Click on the Change button and then click the Yes button on the Dangerous Operation dialog box.

Composite PRIMARY KEY
  1. In the Columns text box, right click on the last column name listed to bring up the context menu and select Edit Column .
  2. Within the Column Type box , append a comma and the PRIMARY KEY definition after data type.
  3. Click on the Change button and then click the Yes button on the Dangerous Operation dialog box.
Column Name Column Type Default Value
Old Values Email INTEGER
New Values EmployeeID INTEGER, PRIMARY KEY (PayPeriod,EmployeeID)
Cancel Change

Add a FOREIGN KEY to an Existing table.

  1. In the Columns text box, right click on the last column name listed to bring up the context menu and select Edit Column .
  2. Within the Column Type box , append a comma and the FOREIGN KEY definition after data type.
  3. Click on the Change button and then click the Yes button on the Dangerous Operation dialog box.

If the last column in TABLE definition is the primary key then the most expedient thing to do it to add new column and then edit the column type of new column to add FOREIGN KEY definition.

Format of a FOREIGN KEY Definition

FOREIGN KEY(foreign_key_field) REFERENCES parent_table_name (parent_key_field)

Column Name Column Type Default Value
Old Values TransNum INTEGER
New Values TransNum INTEGER, FOREIGN KEY(TransNum) REFERENCES Sa
Cancel Change

Colin Riley --Updated January 2011--

Home     SQLite Site Index