Constraints

Constraints enforce data integrity rules at the database level. CockroachDB supports all standard SQL constraints plus additional features for distributed systems.

Constraint Overview

Constraints ensure data validity and consistency across your database:

PRIMARY KEY - Uniquely identifies each row
FOREIGN KEY - Enforces referential integrity between tables
UNIQUE - Ensures column values are unique
CHECK - Validates values against a condition
NOT NULL - Prevents null values
DEFAULT - Provides default values for columns

PRIMARY KEY Constraint

Every table must have a primary key that uniquely identifies each row.

Single Column
Composite
Hash Sharded
Named Constraint

-- Column-level constraint
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50)
);

-- Table-level constraint
CREATE TABLE products (
  product_id INT,
  name TEXT,
  PRIMARY KEY (product_id)
);

-- Multi-column primary key
CREATE TABLE order_items (
  order_id INT,
  item_id INT,
  quantity INT,
  price DECIMAL(10, 2),
  PRIMARY KEY (order_id, item_id)
);

Composite primary keys are useful for junction tables and naturally composite identifiers.

-- Hash-sharded primary key to avoid hotspots
CREATE TABLE events (
  event_id UUID PRIMARY KEY USING HASH WITH (bucket_count = 8),
  event_type TEXT,
  occurred_at TIMESTAMP
);

-- With storage parameters
CREATE TABLE logs (
  log_id INT PRIMARY KEY USING HASH,
  message TEXT
) WITH (sql_stats_automatic_collection_enabled = true);

-- Named primary key constraint
CREATE TABLE customers (
  customer_id INT,
  email VARCHAR(255),
  CONSTRAINT pk_customers PRIMARY KEY (customer_id)
);

Auto-Incrementing Primary Keys

Sequential integer primary keys can create hotspots in distributed systems. Consider UUID or hash-sharded keys instead.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT,
  total DECIMAL(10, 2)
);

-- SERIAL expands to:
-- id INT DEFAULT unique_rowid()

FOREIGN KEY Constraint

Foreign keys enforce referential integrity between tables.

Basic Foreign Key
Composite Foreign Key
Named Foreign Key

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT REFERENCES users(id),
  total DECIMAL(10, 2)
);

-- Table-level syntax
CREATE TABLE order_items (
  order_id INT,
  item_id INT,
  product_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

CREATE TABLE order_items (
  order_id INT,
  item_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, item_id)
);

CREATE TABLE shipment_items (
  shipment_id INT,
  order_id INT,
  item_id INT,
  FOREIGN KEY (order_id, item_id) 
    REFERENCES order_items(order_id, item_id)
);

CREATE TABLE comments (
  id INT PRIMARY KEY,
  post_id INT,
  user_id INT,
  content TEXT,
  CONSTRAINT fk_comments_post 
    FOREIGN KEY (post_id) REFERENCES posts(id),
  CONSTRAINT fk_comments_user 
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Referential Actions

Control what happens when referenced rows are updated or deleted.

-- Delete child rows when parent is deleted
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE
);

-- Deleting a user also deletes their orders
DELETE FROM users WHERE id = 1;

ON DELETE

action

Actions: CASCADE, SET NULL, SET DEFAULT, RESTRICT (default), NO ACTION

ON UPDATE

action

Actions: CASCADE, SET NULL, SET DEFAULT, RESTRICT (default), NO ACTION

Foreign Key Performance

Foreign key checks can impact write performance in distributed systems. Each foreign key validation requires a read from the referenced table.

-- For better performance, ensure foreign key columns are indexed
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT REFERENCES users(id)
);

-- Create index on foreign key column
CREATE INDEX orders_user_id_idx ON orders (user_id);

UNIQUE Constraint

Ensures all values in a column or group of columns are unique.

Column-Level
Table-Level
Composite Unique
Named Unique

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(255) UNIQUE
);

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(255),
  UNIQUE (username),
  UNIQUE (email)
);

CREATE TABLE enrollments (
  id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  semester VARCHAR(20),
  UNIQUE (student_id, course_id, semester)
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  sku VARCHAR(50),
  name TEXT,
  CONSTRAINT uq_products_sku UNIQUE (sku)
);

UNIQUE Without Index

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

-- This creates a unique index automatically

CHECK Constraint

Validates column values against a boolean expression.

Simple Check
Named Check
Multi-Column Check
Complex Expressions

CREATE TABLE products (
  id INT PRIMARY KEY,
  name TEXT,
  price DECIMAL(10, 2) CHECK (price > 0),
  stock INT CHECK (stock >= 0)
);

-- This insert fails
INSERT INTO products VALUES (1, 'Widget', -10, 5);
-- ERROR: check constraint "products_price_check" violated

CREATE TABLE users (
  id INT PRIMARY KEY,
  age INT,
  email VARCHAR(255),
  CONSTRAINT chk_adult_age CHECK (age >= 18),
  CONSTRAINT chk_valid_email CHECK (email LIKE '%@%')
);

CREATE TABLE date_ranges (
  id INT PRIMARY KEY,
  start_date DATE,
  end_date DATE,
  CHECK (end_date > start_date)
);

CREATE TABLE discounts (
  id INT PRIMARY KEY,
  percent_off DECIMAL(5, 2),
  flat_amount DECIMAL(10, 2),
  CHECK (
    (percent_off IS NOT NULL AND flat_amount IS NULL) OR
    (percent_off IS NULL AND flat_amount IS NOT NULL)
  )
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  status TEXT CHECK (status IN ('pending', 'processing', 'shipped', 'delivered')),
  total DECIMAL(10, 2),
  tax DECIMAL(10, 2),
  CHECK (tax >= 0 AND tax <= total * 0.5)
);

CREATE TABLE coordinates (
  id INT PRIMARY KEY,
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),
  CHECK (latitude BETWEEN -90 AND 90),
  CHECK (longitude BETWEEN -180 AND 180)
);

CHECK constraints cannot reference other tables or use subqueries. They are evaluated per row only.

NOT NULL Constraint

Prevents NULL values in a column.

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(20)  -- nullable
);

DEFAULT Constraint

Provides default values when no value is specified.

Constant Defaults
Function Defaults
Expression Defaults
Named Default

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  status VARCHAR(20) DEFAULT 'active',
  role VARCHAR(20) DEFAULT 'user',
  login_count INT DEFAULT 0
);

-- Uses defaults
INSERT INTO users (id, username) VALUES (1, 'alice');

CREATE TABLE events (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  event_type TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO events (event_type) VALUES ('login');

CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_date DATE DEFAULT CURRENT_DATE,
  expires_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days')
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2),
  CONSTRAINT def_price DEFAULT 0.00 FOR price
);

Modify Defaults

ALTER TABLE users ALTER COLUMN status SET DEFAULT 'pending';

ON UPDATE Constraint

Automatically update column values on row updates.

CREATE TABLE documents (
  id INT PRIMARY KEY,
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- updated_at automatically changes on UPDATE
UPDATE documents SET content = 'new content' WHERE id = 1;

Managing Constraints

Adding Constraints

ALTER TABLE users ADD PRIMARY KEY (id);

ALTER TABLE order_items ADD PRIMARY KEY (order_id, item_id);

Dropping Constraints

ALTER TABLE users DROP CONSTRAINT uq_users_email;

ALTER TABLE orders DROP CONSTRAINT fk_orders_user;

Viewing Constraints

SHOW CONSTRAINTS FROM users;

SHOW CONSTRAINTS FROM my_database.public.orders;

Constraint Validation

Immediate vs Deferred

CockroachDB validates constraints immediately by default. Deferred constraint checking is not currently supported.

Disabling Constraint Checks

Disabling constraints can lead to data inconsistency. Use only for data migration or repair scenarios.

-- Foreign key constraint checking cannot be disabled in CockroachDB
-- Instead, drop and recreate the constraint:

ALTER TABLE orders DROP CONSTRAINT fk_orders_user;

-- Perform data operations
-- ...

-- Recreate constraint with validation
ALTER TABLE orders ADD CONSTRAINT fk_orders_user 
  FOREIGN KEY (user_id) REFERENCES users(id);

Best Practices

Constraint Best Practices:

Use PRIMARY KEY on every table - Essential for CockroachDB’s distributed architecture
Prefer UUID over SERIAL - Avoids hotspots in distributed systems
Index foreign key columns - Improves JOIN and foreign key check performance
Use CHECK for domain validation - Enforce business rules at the database level
Name your constraints - Makes error messages and maintenance easier
Use UNIQUE WITHOUT INDEX sparingly - Only when you don’t query by that column
Consider constraint costs - Each constraint adds validation overhead on writes
Use NOT NULL liberally - Makes data requirements explicit and catches bugs early

Advanced Constraint Patterns

Polymorphic Associations

-- Comments can belong to posts or photos
CREATE TABLE comments (
  id INT PRIMARY KEY,
  commentable_type TEXT NOT NULL,
  commentable_id INT NOT NULL,
  content TEXT,
  CHECK (commentable_type IN ('Post', 'Photo')),
  UNIQUE (commentable_type, commentable_id)
);

Conditional Constraints

CREATE TABLE users (
  id INT PRIMARY KEY,
  type TEXT CHECK (type IN ('individual', 'business')),
  ssn VARCHAR(11),
  tax_id VARCHAR(20),
  -- Individuals must have SSN, businesses must have TAX_ID
  CHECK (
    (type = 'individual' AND ssn IS NOT NULL) OR
    (type = 'business' AND tax_id IS NOT NULL)
  )
);

Audit Trail Constraints

CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name TEXT NOT NULL,
  operation TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
  changed_by TEXT NOT NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (changed_at <= CURRENT_TIMESTAMP)
);

Getting Started

Architecture

SQL Reference

Administration

Operations

Performance

Constraint Overview

PRIMARY KEY Constraint

Auto-Incrementing Primary Keys

FOREIGN KEY Constraint

Referential Actions

Foreign Key Performance

UNIQUE Constraint

UNIQUE Without Index

CHECK Constraint

NOT NULL Constraint

DEFAULT Constraint

Modify Defaults

ON UPDATE Constraint

Managing Constraints

Adding Constraints

Dropping Constraints

Viewing Constraints

Constraint Validation

Immediate vs Deferred

Disabling Constraint Checks

Best Practices

Advanced Constraint Patterns

Polymorphic Associations

Conditional Constraints

Audit Trail Constraints

See Also

Build docs developers (and LLMs) love

Getting Started

Architecture

SQL Reference

Administration

Operations

Performance

​Constraint Overview

​PRIMARY KEY Constraint

​Auto-Incrementing Primary Keys

​FOREIGN KEY Constraint

​Referential Actions

​Foreign Key Performance

​UNIQUE Constraint

​UNIQUE Without Index

​CHECK Constraint

​NOT NULL Constraint

​DEFAULT Constraint

​Modify Defaults

​ON UPDATE Constraint

​Managing Constraints

​Adding Constraints

​Dropping Constraints

​Viewing Constraints

​Constraint Validation

​Immediate vs Deferred

​Disabling Constraint Checks

​Best Practices

​Advanced Constraint Patterns

​Polymorphic Associations

​Conditional Constraints

​Audit Trail Constraints

​See Also

Build docs developers (and LLMs) love

Constraint Overview

PRIMARY KEY Constraint

Auto-Incrementing Primary Keys

FOREIGN KEY Constraint

Referential Actions

Foreign Key Performance

UNIQUE Constraint

UNIQUE Without Index

CHECK Constraint

NOT NULL Constraint

DEFAULT Constraint

Modify Defaults

ON UPDATE Constraint

Managing Constraints

Adding Constraints

Dropping Constraints

Viewing Constraints

Constraint Validation

Immediate vs Deferred

Disabling Constraint Checks

Best Practices

Advanced Constraint Patterns

Polymorphic Associations

Conditional Constraints

Audit Trail Constraints

See Also