4.10.4 - SQL table definitions and data changes (VB.NET)

Defining tables and constraints

A table definition specifies the fields that records contain and the constraints that valid records must satisfy.

CREATE TABLE defines a new table; it does not retrieve records or add its first record. A field's data type describes its permitted kind of value. INTEGER suits whole-number identifiers and counts, VARCHAR(40) variable-length text up to a chosen size, DECIMAL(6,2) fixed-point values with six digits in total and two after the decimal point, and DATE a calendar date. Exact enforcement and some type names depend on the database system; use the types and conventions supplied in a task.

For an equipment store, Member(MemberID, MemberName) and Equipment(ItemID, ItemName, DailyCost) already exist, with the first field in each as its primary key. The following creates a related table:

CREATE TABLE Hire (
    HireID INTEGER PRIMARY KEY,
    MemberID INTEGER NOT NULL,
    ItemID INTEGER NOT NULL,
    Days INTEGER NOT NULL CHECK (Days >= 1),
    Returned INTEGER NOT NULL CHECK (Returned IN (0, 1)),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
    FOREIGN KEY (ItemID) REFERENCES Equipment(ItemID)
);

Commas separate field and table-constraint definitions; there is no comma after the last definition. The primary key uniquely identifies each hire. A primary key must be unique and non-NULL. A composite primary key uses a combination of fields, for example PRIMARY KEY (MemberID, ItemID) in a table allowing at most one row for each member–item pair; that would be unsuitable for Hire because the same member can hire the same item on separate occasions.

NOT NULL requires a value. CHECK requires the stated condition; here Days must be positive, and Returned is restricted to 0 or 1. UNIQUE prevents duplicate values in a field or field combination, and DEFAULT supplies a value when an insertion omits that field. A default does not mean every later value must equal it. Pair an obligatory field's range check with NOT NULL; a missing value must not be mistaken for a permitted number.

Each foreign-key constraint links a field to a key in its parent table. Hire 101 may refer to Member 11 only if Member 11 exists. A key is not created merely by giving its field a name ending in ID. The declared relationship lets the database reject an invalid reference. Tables must be created in a suitable order, with the referenced parent tables available before their child table.

Inserting, updating and deleting data

Data-changing statements affect stored records, so identify both the intended new values and the exact records they apply to.

INSERT INTO adds records. An explicit field list connects each supplied value to its destination and avoids reliance on physical column order. Suppose Member 11 and Equipment 10 already exist:

INSERT INTO Hire (HireID, MemberID, ItemID, Days, Returned)
VALUES (101, 11, 10, 2, 0);

The insert creates one outstanding two-day hire. It fails if HireID 101 already exists or either parent key is absent, assuming the declared constraints are enforced. Text values use quotes. This separate insertion creates a member:

INSERT INTO Member (MemberID, MemberName)
VALUES (14, 'Dina');

Do not put field names in the value list as if they were the desired data.

UPDATE changes fields in records that already exist:

UPDATE Hire
SET Days = 3, Returned = 1
WHERE HireID = 101;

SET names the fields and their new values. This statement updates the one identified hire; it does not insert another hire. Multiple assignments are comma-separated. An expression can use the old value: SET DailyCost = DailyCost + 1 increases each selected equipment record's cost by one. WHERE DailyCost = 6 would select by the old cost before that change.

DELETE FROM removes whole records, rather than clearing an individual field:

DELETE FROM Hire
WHERE HireID = 101;

To record an unknown optional value in an existing record, an allowed UPDATE ... SET Field = NULL is a different operation. DROP TABLE removes the table definition and its data, so it is not an alternative to deleting one hire.

In both UPDATE and DELETE, omitting WHERE applies the operation to every row. A broad condition may also affect several rows: WHERE MemberID = 11 targets every hire by that member, whereas WHERE HireID = 101 targets one hire. Before a change, a SELECT using the same condition can show its intended target set. Afterwards, inspect the changed fields and the records that should remain unchanged. A syntactically correct destructive statement can still target the wrong data.