sql / intermediate
Snippet
Enforcing Domain and Referential Integrity with Schema Constraints
Schema constraints enforce business rules directly inside the database engine. CHECK constraints validate value ranges or allowed options, while FOREIGN KEY constraints guarantee referential integrity.
snippet.sql
sql
1
2
3
4
5
6
7
8
9
10
CREATE TABLE account_transactions (transaction_id INTEGER PRIMARY KEY,account_id INTEGER NOT NULL,amount DECIMAL(12, 2) NOT NULL,transaction_type VARCHAR(10) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,CONSTRAINT chk_positive_amount CHECK (amount > 0.00),CONSTRAINT chk_valid_type CHECK (transaction_type IN ('DEPOSIT', 'WITHDRAWAL')),CONSTRAINT fk_account FOREIGN KEY (account_id) REFERENCES accounts(account_id));
Breakdown
1
transaction_id INTEGER PRIMARY KEY,
Sets transaction_id as the unique identifier and primary key.
2
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Assigns the current system timestamp as the default column value.
3
CONSTRAINT chk_positive_amount CHECK (amount > 0.00),
Prevents negative or zero transaction amounts from being inserted.
4
CONSTRAINT chk_valid_type CHECK (transaction_type IN ('DEPOSIT', 'WITHDRAWAL')),
Restricts allowable string values for transaction types.
5
CONSTRAINT fk_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
Ensures account_id must exist in the referenced accounts table.