1.3.2 - SQL data changes and database indexing

Adding a record with INSERT

INSERT adds a record to an existing table; it does not create the table.

Use Workshop(Code TEXT, Title TEXT, Seats INTEGER), where Code is the non-null primary key, Title is non-null text and Seats is a non-null integer greater than or equal to zero. Initially it contains these complete records:

INSERT INTO Workshop (Code, Title, Seats)
VALUES ('W4', 'Photo walk', 6);

The explicit column list states how to interpret the values: W4 goes to Code, Photo walk to Title and 6 to Seats. After insertion, the original three rows remain and a fourth row is present. Text is quoted; the integer seat count has no quotes or unit suffix. Writing an explicit column list avoids relying on a table's physical column order.

Constraints still apply. Inserting W1 again violates the unique primary key even if its other values differ. A negative Seats value violates the stated range constraint, and omitting a required field without a default violates a non-null constraint. The DBMS should reject such invalid changes instead of silently overwriting another record. An INSERT does not mean “replace the matching row”; a separate update operation would be needed for that purpose.

Removing selected rows with DELETE

DELETE removes rows while leaving the table's structure available.

Starting again from the original three Workshop rows:

DELETE FROM Workshop
WHERE Seats = 0;

Only W2 meets the condition, so W1 and W3 remain. The Code, Title and Seats fields and their constraints still exist. A deletion condition may match no rows, one row or many rows; it does not mean “delete just the first match”. DELETE FROM Workshop; has no WHERE condition and removes all three records, leaving an empty Workshop table into which valid records can still be inserted.

Before a destructive change, use the same condition in a SELECT to check which records would be affected, and make sure the user is authorised. In a linked database, deleting a referenced parent may be blocked or may invoke an explicitly chosen cascading policy. A selected row is not necessarily safe to delete merely because it is no longer displayed in a report. Work on a disposable local copy for these examples.

Removing the table with DROP

DROP TABLE removes a table as a database object, including its stored rows and definition.