Gestión de Restricciones en SQLite

Introducción

En este laboratorio, explorarás la gestión de restricciones de SQLite para garantizar la integridad de los datos. Comenzarás definiendo restricciones de clave externa (foreign key constraints) para establecer relaciones entre tablas, implementando restricciones CHECK, creando claves compuestas (composite keys) y, finalmente, probando violaciones de restricciones para comprender cómo SQLite aplica estas reglas. Esta experiencia práctica te proporcionará una comprensión práctica de cómo mantener la consistencia de los datos dentro de tus bases de datos SQLite.

Crear Tablas con una Restricción de Clave Externa

En este paso, crearás dos tablas, customers (clientes) y orders (pedidos), y establecerás una restricción de clave externa entre ellas. Esta restricción garantizará que cada pedido esté asociado con un cliente válido.

Primero, abre la shell de SQLite ejecutando el siguiente comando en tu terminal:

sqlite3 /home/labex/project/database.db

Este comando abre una conexión al archivo de base de datos SQLite llamado database.db. Si el archivo no existe, SQLite lo creará. Ahora deberías ver el prompt sqlite>.

Ahora, crea la tabla customers con el siguiente comando SQL:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

Este comando crea una tabla llamada customers con cuatro columnas: customer_id, first_name, last_name y email. La columna customer_id es la clave primaria (primary key) y se incrementará automáticamente para cada nuevo cliente.

A continuación, crea la tabla orders con una restricción de clave externa que haga referencia a la tabla customers:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date TEXT,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Este comando crea una tabla llamada orders con cuatro columnas: order_id, customer_id, order_date y total. La columna order_id es la clave primaria. La cláusula FOREIGN KEY (customer_id) REFERENCES customers(customer_id) establece una restricción de clave externa, asegurando que el customer_id en la tabla orders haga referencia a un customer_id válido en la tabla customers.

Para verificar que las tablas se han creado, puedes usar el siguiente comando:

.tables

Este comando listará todas las tablas en la base de datos. Deberías ver tanto customers como orders en la salida.

Insertar Datos y Probar la Restricción de Clave Externa

En este paso, insertarás datos en las tablas customers (clientes) y orders (pedidos) y probarás la restricción de clave externa.

Primero, debes habilitar las restricciones de clave externa en SQLite (están deshabilitadas por defecto):

PRAGMA foreign_keys = ON;

Ahora, inserta algunos datos en la tabla customers:

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com');

Este comando inserta dos nuevos clientes en la tabla customers. El customer_id se asignará automáticamente.

Ahora, inserta un pedido en la tabla orders, haciendo referencia a uno de los clientes existentes:

INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2023-01-01', 100.00);

Este comando inserta un nuevo pedido en la tabla orders, asociándolo con el cliente cuyo customer_id es 1.

A continuación, intenta insertar un pedido con un customer_id que no existe en la tabla customers:

INSERT INTO orders (customer_id, order_date, total) VALUES
(99, '2023-01-02', 50.00);

Verás un mensaje de error similar a este: Error: FOREIGN KEY constraint failed. Esto confirma que la restricción de clave externa está funcionando correctamente, impidiéndote crear un pedido para un cliente inexistente.

Para ver los datos en las tablas, puedes usar los siguientes comandos:

SELECT * FROM customers;
SELECT * FROM orders;

Estos comandos mostrarán el contenido de las tablas customers y orders, respectivamente.

Implementar una Restricción CHECK

En este paso, crearás una nueva tabla customers_with_check que incluye una restricción CHECK para asegurar que todas las direcciones de correo electrónico contengan el símbolo @.

Primero, creemos una nueva tabla con la restricción CHECK directamente:

CREATE TABLE customers_with_check (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT CHECK (email LIKE '%@%')
);

Este comando crea una nueva tabla llamada customers_with_check con una restricción CHECK en la columna email que asegura que contenga el símbolo @. El operador LIKE se utiliza para la coincidencia de patrones (pattern matching), y % es un carácter comodín (wildcard character) que coincide con cualquier secuencia de caracteres.

Ahora, copiemos los datos de clientes existentes a la nueva tabla:

INSERT INTO customers_with_check (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email FROM customers;

Ahora, intenta insertar un nuevo cliente con una dirección de correo electrónico no válida:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Charlie', 'Davis', 'invalid-email');

Deberías ver un mensaje de error similar a este: Error: CHECK constraint failed: email. Esto indica que la restricción CHECK impidió la inserción de la dirección de correo electrónico no válida.

Para verificar que la restricción CHECK está funcionando, inserta un cliente con una dirección de correo electrónico válida:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');

Este comando debería ejecutarse correctamente, ya que la dirección de correo electrónico contiene el símbolo @.

Crear una Tabla con una Clave Compuesta

En este paso, crearás una tabla llamada enrollments (inscripciones) con una clave compuesta que consiste en las columnas student_id (id del estudiante) y course_id (id del curso).

Ejecuta la siguiente sentencia SQL en la shell de SQLite:

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date TEXT,
    PRIMARY KEY (student_id, course_id)
);

Este comando crea una tabla llamada enrollments con tres columnas: student_id, course_id y enrollment_date (fecha de inscripción). La cláusula PRIMARY KEY (student_id, course_id) especifica que la clave primaria (primary key) para la tabla consiste tanto en las columnas student_id como en course_id. Esto significa que la combinación de student_id y course_id debe ser única para cada fila (row) en la tabla.

Ahora, inserta algunos datos en la tabla enrollments:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-02'),
(1, 102, '2023-01-03');

Esto insertará tres filas en la tabla enrollments.

A continuación, intenta insertar una fila con el mismo student_id y course_id que una fila existente:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-04');

Deberías ver un mensaje de error similar a este: Error: UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. Esto indica que la restricción de clave compuesta impidió la inserción de la fila duplicada.

Para verificar que la tabla se ha creado correctamente, puedes usar el comando .tables en la shell de SQLite:

.tables

Deberías ver enrollments en la salida.

Resumen

En este laboratorio, has aprendido cómo definir e implementar restricciones (constraints) en SQLite para asegurar la integridad de los datos. Creaste tablas con restricciones de clave externa (foreign key constraints), implementaste restricciones CHECK para validar datos, y creaste tablas con claves compuestas (composite keys) para identificar de forma única las filas (rows) basándote en múltiples columnas. Al comprender y utilizar estas restricciones, puedes construir bases de datos robustas y confiables que mantengan la consistencia de los datos.