The relational model and constraints

A relational database stores data in relations, which SQL calls tables. A table has named columns with types, and each row is one record. The model was introduced by E. F. Codd in 1970 and is the basis of systems such as PostgreSQL, MySQL, SQL Server, Oracle, and SQLite.

Python

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.execute("CREATE TABLE department (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)")
db.execute(
    "CREATE TABLE employee ("
    "id INTEGER PRIMARY KEY, name TEXT NOT NULL, "
    "salary INTEGER NOT NULL CHECK (salary > 0), "
    "department_id INTEGER NOT NULL REFERENCES department(id))"
)
db.execute("INSERT INTO department (id, name) VALUES (1, 'Engineering')")
db.execute("INSERT INTO employee (name, salary, department_id) VALUES ('Ada', 120, 1)")

bad_statements = [
    "INSERT INTO employee (name, salary, department_id) VALUES ('Bob', 100, 99)",
    "INSERT INTO employee (name, salary, department_id) VALUES ('Cy', -5, 1)",
    "INSERT INTO department (id, name) VALUES (2, 'Engineering')",
]
for statement in bad_statements:
    try:
        db.execute(statement)
    except sqlite3.IntegrityError as error:
        print(str(error).split(":")[0])
print(db.execute("SELECT COUNT(*) FROM employee").fetchone()[0])

Read the full lesson