SQL Constraints

Constraint	Ensures
NOT NULL	A column can't be empty
UNIQUE	All values are different
PRIMARY KEY	NOT NULL + UNIQUE; identifies each row
FOREIGN KEY	The value exists in the referenced table
CHECK	Values satisfy a condition
DEFAULT	Value used when none is given

SQL

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    amount      DECIMAL(10,2) CHECK (amount >= 0),
    status      VARCHAR(10) DEFAULT 'NEW',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
);

Referential Actions

CASCADE: delete or update child rows too.
SET NULL: set the foreign key to NULL.
RESTRICT / NO ACTION: block the change.

Transactions

SQL

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;          -- or ROLLBACK TO after_debit;

Track your progress through the SQL course

← Prev Next →