Modifying data: INSERT, UPDATE, DELETE and constraints — Design, normalization and data modification

The data manipulation language (DML)

While SELECT reads, these three statements modify the content of the tables.

INSERT — add rows

INSERT INTO customers (id, name, email, active)
VALUES (4, 'Sara Vega', 'sara@example.com', 1);

You specify the table, the columns and the values. Columns with DEFAULT or that accept NULL can be omitted.

UPDATE — change existing rows

UPDATE orders SET status = 'paid' WHERE id = 2;

⚠️ Without WHERE, UPDATE modifies ALL rows. The WHERE decides which rows are changed.

DELETE — remove rows

DELETE FROM orders WHERE status = 'cancelled';

⚠️ Just like UPDATE: without WHERE, DELETE empties the whole table.

Constraints

Constraints are declared in the CREATE TABLE and make the database reject invalid data, protecting integrity:

Constraint	What it guarantees
`NOT NULL`	the column cannot be left empty
`UNIQUE`	the value is not repeated (e.g. an email)
`CHECK (cond)`	the value satisfies a condition (`total >= 0`)
`DEFAULT v`	default value if none is given
`FOREIGN KEY`	the value must exist in the referenced table

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending',
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Designing with constraints means that many errors become impossible: an order with a negative total can't sneak in, nor two customers with the same email, nor an orphan order without a customer.