Tutorial SQL: Restricciones e integridad

PRIMARY KEY y UNIQUE

Las restricciones son reglas que se aplican a las columnas de una tabla para mantener la integridad de los datos. Entre las restricciones más importantes en SQL se encuentran PRIMARY KEY y UNIQUE, que nos ayudan a garantizar la unicidad de los valores en determinadas columnas.

Restricción PRIMARY KEY

La restricción PRIMARY KEY identifica de manera única cada registro en una tabla. Actúa como un identificador que no puede repetirse ni contener valores nulos, lo que la convierte en la base para establecer relaciones entre tablas.

Puedes definir una PRIMARY KEY de dos formas principales:

• 1. Al crear la tabla (forma más común):

CREATE TABLE clientes (
    id_cliente INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

• 2. Como restricción separada (útil para claves compuestas):

CREATE TABLE pedidos (
    id_pedido INT,
    id_producto INT,
    cantidad INT NOT NULL,
    PRIMARY KEY (id_pedido, id_producto)
);

En MySQL, también puedes usar la opción AUTO_INCREMENT para generar valores únicos automáticamente:

CREATE TABLE productos (
    id_producto INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2)
);

En PostgreSQL, se utiliza la palabra clave SERIAL o IDENTITY:

CREATE TABLE productos (
    id_producto SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2)
);

O con la sintaxis más moderna (PostgreSQL 10+):

CREATE TABLE productos (
    id_producto INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2)
);

Características importantes de PRIMARY KEY:

• Solo puede existir una PRIMARY KEY por tabla
• No permite valores NULL
• Crea automáticamente un índice único para mejorar el rendimiento de las consultas
• Es la base para establecer relaciones entre tablas mediante FOREIGN KEY

Restricción UNIQUE

La restricción UNIQUE garantiza que todos los valores en una columna (o conjunto de columnas) sean diferentes. A diferencia de PRIMARY KEY, una tabla puede tener múltiples restricciones UNIQUE.

• 1. Al crear la tabla:

CREATE TABLE usuarios (
    id_usuario INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    nombre_usuario VARCHAR(50) UNIQUE
);

• 2. Como restricción separada:

CREATE TABLE empleados (
    id_empleado INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    numero_empleado VARCHAR(20),
    UNIQUE (email),
    UNIQUE (numero_empleado)
);

• 3. Restricción UNIQUE con nombre personalizado:

CREATE TABLE productos (
    id_producto INT PRIMARY KEY,
    codigo_barras VARCHAR(13),
    sku VARCHAR(20),
    CONSTRAINT uk_codigo_barras UNIQUE (codigo_barras),
    CONSTRAINT uk_sku UNIQUE (sku)
);

• 4. Restricción UNIQUE para múltiples columnas:

CREATE TABLE inscripciones (
    id_inscripcion INT PRIMARY KEY,
    id_estudiante INT,
    id_curso INT,
    fecha_inscripcion DATE,
    UNIQUE (id_estudiante, id_curso)
);

Características importantes de UNIQUE:

• Una tabla puede tener múltiples restricciones UNIQUE
• A diferencia de PRIMARY KEY, permite valores NULL (aunque solo uno, ya que NULL no se considera igual a otro NULL en este contexto)
• También crea un índice único para mejorar el rendimiento
• Es útil para columnas que requieren unicidad pero no son la clave principal

Diferencias entre PRIMARY KEY y UNIQUE

Modificar restricciones en tablas existentes

Si necesitas agregar estas restricciones a tablas ya existentes, puedes usar ALTER TABLE:

• Agregar PRIMARY KEY:

ALTER TABLE productos
ADD PRIMARY KEY (id_producto);

• Agregar UNIQUE:

ALTER TABLE usuarios
ADD CONSTRAINT uk_email UNIQUE (email);

• Eliminar restricciones:

-- Eliminar PRIMARY KEY
ALTER TABLE productos
DROP PRIMARY KEY;

-- Eliminar UNIQUE
ALTER TABLE usuarios
DROP CONSTRAINT uk_email;

Buenas prácticas

• Nombra tus restricciones: Usar nombres descriptivos facilita el mantenimiento.
• Usa PRIMARY KEY para identificadores principales: Cada tabla debería tener una clave primaria.
• Usa UNIQUE para datos que no deben duplicarse: Como emails, nombres de usuario o códigos de producto.
• Considera claves compuestas cuando sea apropiado: A veces la unicidad depende de la combinación de varias columnas.
• Prefiere tipos de datos numéricos para las claves primarias por razones de rendimiento.

Ejemplo práctico

Veamos un ejemplo completo de una base de datos para una biblioteca:

-- Tabla de autores
CREATE TABLE autores (
    id_autor INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    fecha_nacimiento DATE
);

-- Tabla de libros
CREATE TABLE libros (
    isbn VARCHAR(13) PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    id_autor INT NOT NULL,
    anio_publicacion INT,
    codigo_interno VARCHAR(20) UNIQUE
);

-- Tabla de préstamos (con clave primaria compuesta)
CREATE TABLE prestamos (
    id_usuario INT,
    isbn VARCHAR(13),
    fecha_prestamo DATE NOT NULL,
    fecha_devolucion DATE,
    PRIMARY KEY (id_usuario, isbn, fecha_prestamo)
);

En este ejemplo:

• Cada autor tiene un ID único y un email único
• Cada libro se identifica por su ISBN único y también tiene un código interno único
• Los préstamos se identifican por la combinación de usuario, libro y fecha

Las restricciones PRIMARY KEY y UNIQUE son fundamentales para mantener la integridad de los datos en cualquier base de datos relacional. Proporcionan la base para las relaciones entre tablas y garantizan que los datos críticos no se dupliquen accidentalmente.

FOREIGN KEY

La restricción FOREIGN KEY (clave foránea) es un componente esencial en el diseño de bases de datos relacionales que establece y mantiene la integridad referencial entre tablas. Mientras que PRIMARY KEY y UNIQUE se centran en la unicidad de los datos, FOREIGN KEY garantiza que las relaciones entre tablas sean válidas y consistentes.

Concepto y propósito

Una clave foránea es un campo (o conjunto de campos) en una tabla que hace referencia a la clave primaria de otra tabla. Esta restricción crea una relación padre-hijo entre las tablas, donde:

• La tabla que contiene la clave foránea se denomina tabla "hija" o "referenciadora"
• La tabla a la que hace referencia (que contiene la clave primaria) se denomina tabla "padre" o "referenciada"

El propósito principal de una FOREIGN KEY es prevenir acciones que destruirían los vínculos entre tablas, asegurando que:

1. Solo se puedan insertar en la tabla hija valores que existan en la tabla padre
2. Los cambios en la tabla padre se gestionen adecuadamente en la tabla hija

Sintaxis básica

La sintaxis para crear una restricción FOREIGN KEY puede variar ligeramente entre MySQL y PostgreSQL, pero el concepto es el mismo:

• Al crear una tabla:

CREATE TABLE pedidos (
    id_pedido INT PRIMARY KEY,
    id_cliente INT,
    fecha_pedido DATE NOT NULL,
    total DECIMAL(10,2),
    FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente)
);

• Con nombre de restricción explícito:

CREATE TABLE pedidos (
    id_pedido INT PRIMARY KEY,
    id_cliente INT,
    fecha_pedido DATE NOT NULL,
    total DECIMAL(10,2),
    CONSTRAINT fk_cliente FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente)
);

• Añadir a una tabla existente:

ALTER TABLE pedidos
ADD CONSTRAINT fk_cliente FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente);

Acciones referenciales

Uno de los aspectos más potentes de las claves foráneas es la capacidad de definir acciones referenciales que determinan qué sucede cuando se actualiza o elimina un registro en la tabla padre. Estas acciones se especifican mediante las cláusulas ON DELETE y ON UPDATE:

CREATE TABLE detalles_pedido (
    id_pedido INT,
    id_producto INT,
    cantidad INT NOT NULL,
    precio_unitario DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id_pedido, id_producto),
    FOREIGN KEY (id_pedido) REFERENCES pedidos(id_pedido)
        ON DELETE CASCADE
        ON UPDATE RESTRICT
);

Las opciones disponibles para estas acciones son:

• CASCADE: Propaga la acción (eliminación o actualización) a los registros relacionados en la tabla hija
• RESTRICT: Impide la acción en la tabla padre si existen registros relacionados en la tabla hija
• NO ACTION: Similar a RESTRICT, pero se verifica al final de la transacción (comportamiento predeterminado en muchos sistemas)
• SET NULL: Establece como NULL los valores de la clave foránea en la tabla hija
• SET DEFAULT: Establece los valores de la clave foránea en la tabla hija a sus valores predeterminados

Ejemplo de diferentes acciones referenciales:

-- Eliminar productos elimina automáticamente sus líneas de pedido
FOREIGN KEY (id_producto) REFERENCES productos(id_producto) ON DELETE CASCADE

-- Impedir la eliminación de clientes que tienen pedidos
FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente) ON DELETE RESTRICT

-- Al eliminar una categoría, los productos quedan sin categoría
FOREIGN KEY (id_categoria) REFERENCES categorias(id_categoria) ON DELETE SET NULL

Claves foráneas compuestas

Al igual que las claves primarias, las claves foráneas pueden estar compuestas por múltiples columnas:

CREATE TABLE reservas_asientos (
    id_vuelo VARCHAR(10),
    numero_asiento VARCHAR(5),
    id_pasajero INT,
    PRIMARY KEY (id_vuelo, numero_asiento),
    FOREIGN KEY (id_vuelo, numero_asiento) REFERENCES asientos_avion(id_vuelo, numero_asiento)
);

En este caso, la combinación de id_vuelo y numero_asiento debe existir en la tabla asientos_avion.

Consideraciones de rendimiento

Las claves foráneas tienen implicaciones importantes en el rendimiento:

• Verificación de integridad: Cada operación INSERT o UPDATE en la tabla hija requiere una verificación en la tabla padre
• Índices: Es recomendable que las columnas de clave foránea estén indexadas para mejorar el rendimiento de estas verificaciones
• Operaciones en lote: Las operaciones masivas pueden ser más lentas debido a las comprobaciones de integridad

MySQL crea automáticamente un índice en las columnas de clave foránea, mientras que PostgreSQL requiere que lo crees manualmente:

-- En PostgreSQL, es recomendable crear un índice para la clave foránea
CREATE INDEX idx_pedidos_cliente ON pedidos(id_cliente);

Desactivación temporal de restricciones

En algunas situaciones, como migraciones de datos o cargas masivas, puede ser útil desactivar temporalmente las restricciones de clave foránea:

En MySQL:

-- Desactivar verificación de claves foráneas
SET FOREIGN_KEY_CHECKS = 0;

-- Realizar operaciones...

-- Reactivar verificación
SET FOREIGN_KEY_CHECKS = 1;

En PostgreSQL:

-- Desactivar restricciones para una tabla específica
ALTER TABLE pedidos DISABLE TRIGGER ALL;

-- Realizar operaciones...

-- Reactivar restricciones
ALTER TABLE pedidos ENABLE TRIGGER ALL;

Ejemplo práctico: Sistema de comercio electrónico

Veamos un ejemplo completo de un esquema básico para un sistema de comercio electrónico:

-- Tabla de clientes
CREATE TABLE clientes (
    id_cliente INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    fecha_registro DATE NOT NULL
);

-- Tabla de categorías de productos
CREATE TABLE categorias (
    id_categoria INT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    descripcion TEXT
);

-- Tabla de productos
CREATE TABLE productos (
    id_producto INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    id_categoria INT,
    CONSTRAINT fk_categoria FOREIGN KEY (id_categoria) 
        REFERENCES categorias(id_categoria)
        ON DELETE SET NULL
);

-- Tabla de pedidos
CREATE TABLE pedidos (
    id_pedido INT PRIMARY KEY,
    id_cliente INT NOT NULL,
    fecha_pedido TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    estado VARCHAR(20) NOT NULL,
    CONSTRAINT fk_cliente FOREIGN KEY (id_cliente) 
        REFERENCES clientes(id_cliente)
        ON DELETE RESTRICT
);

-- Tabla de detalles de pedido
CREATE TABLE detalles_pedido (
    id_pedido INT,
    id_producto INT,
    cantidad INT NOT NULL,
    precio_unitario DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id_pedido, id_producto),
    CONSTRAINT fk_pedido FOREIGN KEY (id_pedido) 
        REFERENCES pedidos(id_pedido)
        ON DELETE CASCADE,
    CONSTRAINT fk_producto FOREIGN KEY (id_producto) 
        REFERENCES productos(id_producto)
        ON DELETE RESTRICT
);

En este ejemplo:

• Un cliente puede realizar múltiples pedidos (relación uno a muchos)
• Un producto pertenece a una categoría (relación muchos a uno)
• Un pedido contiene múltiples productos y un producto puede estar en múltiples pedidos (relación muchos a muchos resuelta mediante la tabla detalles_pedido)
• Si se elimina un cliente, se impide la operación si tiene pedidos (RESTRICT)
• Si se elimina una categoría, los productos quedan sin categoría (SET NULL)
• Si se elimina un pedido, se eliminan automáticamente sus detalles (CASCADE)
• Si se intenta eliminar un producto que está en algún pedido, se impide la operación (RESTRICT)

Buenas prácticas

• Nombra tus restricciones: Usa prefijos como fk_ para identificar fácilmente las claves foráneas.
• Considera cuidadosamente las acciones referenciales: Elige CASCADE, RESTRICT, SET NULL según la lógica de negocio.
• Indexa las columnas de clave foránea: Mejora el rendimiento de las consultas y verificaciones.
• Mantén la coherencia de tipos de datos: La clave foránea debe tener el mismo tipo de datos que la clave primaria a la que hace referencia.
• Documenta el modelo de datos: Las relaciones entre tablas son fundamentales para entender la estructura de la base de datos.

Las claves foráneas son una herramienta fundamental para mantener la integridad referencial en bases de datos relacionales, asegurando que las relaciones entre entidades sean válidas y consistentes a lo largo del tiempo.

CHECK y DEFAULT

Las restricciones CHECK y DEFAULT son mecanismos fundamentales para mantener la integridad de los datos en bases de datos relacionales. Mientras que PRIMARY KEY, UNIQUE y FOREIGN KEY controlan la unicidad y las relaciones entre tablas, CHECK y DEFAULT se centran en validar y proporcionar valores predeterminados para las columnas.

Restricción CHECK

La restricción CHECK permite definir condiciones personalizadas que deben cumplir los valores de una columna o conjunto de columnas. Actúa como un guardián que valida los datos antes de permitir su inserción o actualización en la tabla.

Sintaxis básica

La forma más común de definir una restricción CHECK es durante la creación de la tabla:

CREATE TABLE productos (
    id_producto INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) CHECK (precio > 0),
    stock INT CHECK (stock >= 0)
);

También puedes definir la restricción con un nombre explícito para facilitar su gestión:

CREATE TABLE empleados (
    id_empleado INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    salario DECIMAL(10,2),
    departamento VARCHAR(50),
    CONSTRAINT chk_salario_positivo CHECK (salario > 0)
);

Las restricciones CHECK pueden involucrar múltiples columnas y expresiones complejas:

CREATE TABLE pedidos (
    id_pedido INT PRIMARY KEY,
    fecha_pedido DATE NOT NULL,
    fecha_entrega DATE,
    estado VARCHAR(20),
    CONSTRAINT chk_fechas CHECK (fecha_entrega >= fecha_pedido),
    CONSTRAINT chk_estado CHECK (estado IN ('Pendiente', 'Enviado', 'Entregado', 'Cancelado'))
);

Añadir restricciones CHECK a tablas existentes

Puedes agregar restricciones CHECK a tablas ya creadas mediante ALTER TABLE:

ALTER TABLE productos
ADD CONSTRAINT chk_precio_minimo CHECK (precio >= 1.99);

Es importante tener en cuenta que al añadir una restricción CHECK a una tabla con datos existentes, estos serán validados contra la nueva restricción. Si algún registro no cumple la condición, la operación fallará.

Ejemplos prácticos de restricciones CHECK

• Validación de rangos numéricos:

CREATE TABLE calificaciones (
    id_estudiante INT,
    id_curso INT,
    calificacion DECIMAL(4,2),
    PRIMARY KEY (id_estudiante, id_curso),
    CONSTRAINT chk_calificacion CHECK (calificacion >= 0 AND calificacion <= 10)
);

• Validación de formatos y patrones (PostgreSQL):

CREATE TABLE usuarios (
    id_usuario INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    codigo_postal VARCHAR(5),
    CONSTRAINT chk_email CHECK (email LIKE '%@%.%'),
    CONSTRAINT chk_cp CHECK (codigo_postal ~ '^[0-9]{5}$')
);

• Validación de relaciones entre columnas:

CREATE TABLE productos_descuento (
    id_producto INT PRIMARY KEY,
    precio_regular DECIMAL(10,2) NOT NULL,
    precio_descuento DECIMAL(10,2),
    CONSTRAINT chk_descuento CHECK (precio_descuento IS NULL OR precio_descuento < precio_regular)
);

Restricción DEFAULT

La restricción DEFAULT especifica un valor predeterminado para una columna cuando no se proporciona un valor explícito en una operación INSERT. Es especialmente útil para establecer valores comunes, marcas de tiempo o estados iniciales.

Sintaxis básica

CREATE TABLE usuarios (
    id_usuario INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    activo BOOLEAN DEFAULT TRUE,
    fecha_registro DATE DEFAULT CURRENT_DATE
);

En este ejemplo, si no se especifica un valor para activo o fecha_registro al insertar un nuevo usuario, se utilizarán automáticamente los valores predeterminados.

Ejemplos de valores DEFAULT comunes

• Valores booleanos:

CREATE TABLE configuraciones (
    id_configuracion INT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    habilitada BOOLEAN DEFAULT FALSE
);

• Fechas y horas actuales:

En MySQL:

CREATE TABLE registros (
    id_registro INT PRIMARY KEY,
    descripcion TEXT,
    creado_en DATETIME DEFAULT CURRENT_TIMESTAMP,
    actualizado_en DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

En PostgreSQL:

CREATE TABLE registros (
    id_registro SERIAL PRIMARY KEY,
    descripcion TEXT,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• Valores numéricos y textuales:

CREATE TABLE productos (
    id_producto INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    descuento DECIMAL(5,2) DEFAULT 0.00,
    unidad VARCHAR(20) DEFAULT 'unidad',
    disponible BOOLEAN DEFAULT TRUE
);

• Expresiones y funciones (PostgreSQL):

CREATE TABLE documentos (
    id_documento SERIAL PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    contenido TEXT,
    version INT DEFAULT 1,
    hash_contenido VARCHAR(64) DEFAULT md5(random()::text)
);

Modificar valores DEFAULT en tablas existentes

Puedes cambiar o añadir valores predeterminados a columnas existentes:

-- Añadir un valor predeterminado
ALTER TABLE usuarios
ALTER COLUMN ultimo_acceso SET DEFAULT CURRENT_TIMESTAMP;

-- Eliminar un valor predeterminado
ALTER TABLE usuarios
ALTER COLUMN nivel_acceso DROP DEFAULT;

Combinando CHECK y DEFAULT

Las restricciones CHECK y DEFAULT a menudo se utilizan juntas para garantizar tanto la validez como la completitud de los datos:

CREATE TABLE articulos (
    id_articulo INT PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    contenido TEXT,
    estado VARCHAR(20) DEFAULT 'Borrador' CHECK (estado IN ('Borrador', 'Publicado', 'Archivado')),
    visitas INT DEFAULT 0 CHECK (visitas >= 0),
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_publicacion TIMESTAMP,
    CONSTRAINT chk_fechas CHECK (fecha_publicacion IS NULL OR fecha_publicacion >= fecha_creacion)
);

En este ejemplo:

• Los artículos comienzan en estado 'Borrador' por defecto
• El contador de visitas comienza en 0
• Se registra automáticamente la fecha de creación
• Se verifica que la fecha de publicación (si existe) sea posterior a la de creación

Diferencias entre MySQL y PostgreSQL

Aunque la sintaxis básica es similar, existen algunas diferencias importantes:

MySQL

• Las restricciones CHECK fueron ignoradas en versiones anteriores a MySQL 8.0.16
• Soporta ON UPDATE CURRENT_TIMESTAMP para actualizar automáticamente columnas de tipo fecha/hora
• Tiene limitaciones en las expresiones que pueden usarse en restricciones CHECK

-- Ejemplo específico de MySQL
CREATE TABLE productos (
    id_producto INT PRIMARY KEY,
    nombre VARCHAR(100),
    precio DECIMAL(10,2),
    ultima_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT chk_precio CHECK (precio > 0)
);

PostgreSQL

• Soporta CHECK desde hace mucho tiempo con funcionalidad completa
• Permite expresiones más complejas, incluyendo subconsultas en algunas situaciones
• Ofrece tipos de datos y operadores adicionales para validaciones avanzadas

-- Ejemplo específico de PostgreSQL
CREATE TABLE productos (
    id_producto SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    precio DECIMAL(10,2),
    ultima_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_precio CHECK (precio > 0)
);

-- Crear una función trigger para actualizar el timestamp
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.ultima_actualizacion = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_producto_timestamp
BEFORE UPDATE ON productos
FOR EACH ROW EXECUTE FUNCTION update_modified_column();

Ejemplo práctico: Sistema de gestión de inventario

Veamos un ejemplo completo que utiliza CHECK y DEFAULT para un sistema de gestión de inventario:

CREATE TABLE categorias (
    id_categoria SERIAL PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    activa BOOLEAN DEFAULT TRUE
);

CREATE TABLE productos (
    id_producto SERIAL PRIMARY KEY,
    codigo VARCHAR(20) UNIQUE NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    precio_compra DECIMAL(10,2) NOT NULL CHECK (precio_compra >= 0),
    precio_venta DECIMAL(10,2) NOT NULL CHECK (precio_venta >= 0),
    stock_actual INT DEFAULT 0 CHECK (stock_actual >= 0),
    stock_minimo INT DEFAULT 5 CHECK (stock_minimo >= 0),
    id_categoria INT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    descontinuado BOOLEAN DEFAULT FALSE,
    CONSTRAINT chk_precios CHECK (precio_venta > precio_compra),
    CONSTRAINT fk_categoria FOREIGN KEY (id_categoria) REFERENCES categorias(id_categoria)
);

CREATE TABLE movimientos_inventario (
    id_movimiento SERIAL PRIMARY KEY,
    id_producto INT NOT NULL,
    tipo_movimiento VARCHAR(20) NOT NULL DEFAULT 'Entrada' CHECK (tipo_movimiento IN ('Entrada', 'Salida', 'Ajuste')),
    cantidad INT NOT NULL CHECK (cantidad > 0),
    fecha_movimiento TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    motivo VARCHAR(100) DEFAULT 'Operación regular',
    CONSTRAINT fk_producto FOREIGN KEY (id_producto) REFERENCES productos(id_producto)
);

En este ejemplo:

• Las categorías están activas por defecto
• Los productos tienen precios de compra y venta que deben ser positivos
• El precio de venta debe ser mayor que el precio de compra
• El stock inicial es 0 y no puede ser negativo
• Cada producto tiene un stock mínimo predeterminado de 5 unidades
• Los movimientos de inventario pueden ser de entrada, salida o ajuste
• Todos los movimientos registran automáticamente la fecha y hora

Buenas prácticas

• Usa nombres descriptivos para las restricciones: Facilita la identificación de errores y el mantenimiento.
• Combina CHECK con NOT NULL cuando sea necesario: CHECK (precio > 0) no impide valores NULL, usa también NOT NULL si quieres evitarlos.
• Mantén las restricciones simples: Divide validaciones complejas en múltiples restricciones CHECK para facilitar la depuración.
• Considera el rendimiento: Las restricciones CHECK muy complejas pueden afectar el rendimiento de las operaciones INSERT y UPDATE.
• Documenta el propósito de cada restricción: Especialmente para validaciones de negocio no obvias.
• Usa DEFAULT para valores comunes: Reduce errores y simplifica las consultas de inserción.
• Valida los datos antes de migrarlos: Al añadir restricciones a tablas existentes, verifica primero si los datos cumplen las condiciones.

Las restricciones CHECK y DEFAULT son herramientas esenciales para implementar reglas de negocio directamente en la estructura de la base de datos, garantizando que los datos sean no solo relacionalmente íntegros, sino también lógicamente válidos según los requisitos específicos de la aplicación.