Managing tables

DB Manager provides functions to create, rename, edit, delete, and empty tables using tools found under the Table menu. In this section, we will discuss each tool.

Creating a new table

Creating new tables using DB Manager is fairly straightforward. When creating a new table, you can specify whether it will be a spatial table or a nonspatial table. In this section, we will create a new spatial table in SpatiaLite to hold data about mountain peaks in a park; to do this, perform the following steps:

Tip

To quickly create a new SpatiaLite layer (and optionally a database) in one dialog box in QGIS Desktop, navigate to Layer | Create Layer | New SpatiaLite Layer….

  1. Open DB Manager by clicking on DB Manager under Database. Expand SpatiaLite and select GiffordPinochet.sqlite in the Tree panel.
  2. Navigate to Table | Create Table to open the Create Table window.
  3. Enter Peaks as the table name.
  4. Click on the Add field button to add a new table field. A new row will appear in the field list. Set the Name field to Name and the Type field to character(20) from the list of field type options.
  5. Click on the Add field button to add a second field, with the Name field set to Elevation and the Type field set to integer.
  6. Set the Primary key field to Name. This will require the peak names to be unique.
  7. Select Create geometry column and choose the following options:
    • Create geometry column: POINT
    • Name: geom
    • Dimensions: 2
    • SRID: 26910
  8. Select Create spatial index to create a spatial index for the table.
  9. Your dialog should look like the following screenshot. If it does, click on the Create button to create the new table:
    Creating a new table
  10. If the table is created successfully, a prompt will confirm that everything went fine. Dismiss the dialog, then click on the Close button to close the Create Table window.
  11. To view the new Peaks table, you'll need to refresh the Tree panel by selecting GiffordPinochet.sqlite in the tree, and then click on Refresh under Database, or press the f5 key on your keyboard. Note that the Peaks table has the point icon, indicating that it is a geometry table.

Renaming a table

To rename a table, perform the following steps:

  1. Open DB Manager by clicking on DB Manager under Database. In the Tree panel, expand the tree and select the database that contains the table that you wish to rename.
  2. In the Tree panel, select the table you wish to edit. Right-click on the table and choose Rename from the contextual menu to rename it.

Editing table properties

To edit table properties, perform the following steps:

  1. Open DB Manager by clicking on DB Manager under Database. In the Tree panel, expand the tree and select the database that contains the table that you wish to edit.
  2. In the Tree panel, select the table that you wish to edit. Navigate to Table | Edit table to open the Table properties window.
  3. The Table properties window (shown in the following screenshot) has three tabs—Columns, Constraints, and Indexes—that allow the editing of their respective table properties.

    The following screenshot shows the column properties for a database table:

    Editing table properties
  4. The Columns tab lists all the fields, their type, whether they allow null values, and their default values. Below the field list, there are four buttons. The Add column button opens a window and allows you to create a new field and specify its properties. The Add geometry column button opens a window and allows you to create a new geometry field and specify its properties. The Edit column button opens a window and lets you change the selected field's properties. The Delete column button deletes the selected field.

    Tip

    SpatiaLite does not support table-altering commands, such as editing and deleting existing fields; therefore, these options will be disabled.

  5. The Constraints tab lists all the constraints on the table; their name, their type, and the column(s) that are affected by the constraints. The Add primary key/unique button opens a window and allows you to create a new primary key constraint. The Delete constraint button deletes the selected constraint.

    Tip

    SpatiaLite does not support adding or removing a constraint from an existing table; therefore, these options will be disabled. The constraints can be managed using other SQLite clients.

  6. The Indexes tab lists all the indexes on the table, their name, and the column(s) that are a part of the index. The Add index button opens a window that allows you to create a new index by selecting the field to index, and provides an index name. The Add spatial index button adds a spatial index to the table. This option is only available if the table is a geometry field. The Delete index button deletes the currently selected index.

Deleting a table

There are two ways to delete a table from a database within QGIS: by using the Browser panel in QGIS Desktop, or by using the DB Manager.

To delete a table using the Browser panel in QGIS Desktop, expand the database from which you wish to delete a table, then right-click on the table and choose Delete layer.

To delete a table using DB Manager, open DB Manager by clicking on DB Manager under Database. In the Tree panel, expand the tree and select the database that contains the table that you wish to delete. In the Tree panel, select the table that you wish to delete. Then, click on Delete table/view under Table. You can also right-click on the table in the Tree panel and choose Delete from the contextual menu.

Emptying a table

To remove every record from a table without deleting the table, open DB Manager by clicking on DB Manager under Database. In the Tree panel, expand the tree and select the database that contains the table that you wish to empty. In the Tree panel, select the table you wish to empty. Then, click on Empty table under Table.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.129.23.30