Módulo 28: Restricciones de integridad
Objetivos del módulo
- Comprender qué es una restricción de integridad y por qué debe declararse en la base de datos y no solo en las aplicaciones
- Definir restricciones en línea y fuera de línea, y darles nombres significativos
- Dominar
NOT NULL,PRIMARY KEY,UNIQUE,FOREIGN KEYyCHECK, con sus casos especiales y su relación con los valores nulos - Elegir la acción referencial adecuada (
NO ACTION,ON DELETE CASCADE,ON DELETE SET NULL) y conocer las alternativas aON UPDATE CASCADE - Gestionar los estados de una restricción (
ENABLE/DISABLE,VALIDATE/NOVALIDATE) y las restricciones diferibles - Reconocer y provocar los errores típicos (
ORA-00001,ORA-01400,ORA-02290,ORA-02291,ORA-02292…) - Consultar las restricciones en el diccionario de datos
- Usar los dominios SQL de Oracle 23ai y conocer las aserciones de Oracle 26ai
- Traducir a SQL las restricciones de un diagrama E/R y saber cuáles no pueden declararse
- Analizar a fondo el script de EMPRESA y diseñar bases de datos completas con su script DDL
1. Qué son las restricciones de integridad
📘 Concepto: Una restricción de integridad (integrity constraint) es una regla que deben cumplir los datos de una base de datos en todo momento. Se declara una vez en el esquema y el SGBD la comprueba automáticamente en cada
INSERT,UPDATEyDELETE, rechazando cualquier operación que la incumpla.
En el módulo 16 estudiaste las reglas de integridad del modelo relacional. Ahora vas a verlas convertidas en sintaxis de Oracle:
| Regla del modelo relacional | Restricción de Oracle | Código en el diccionario |
|---|---|---|
| Obligatoriedad de un atributo | NOT NULL | C |
| Integridad de entidad (clave primaria única y no nula) | PRIMARY KEY | P |
| Claves alternativas (candidatas no elegidas) | UNIQUE | U |
| Integridad referencial | FOREIGN KEY ... REFERENCES | R |
| Restricciones de dominio y reglas de negocio de una fila | CHECK | C |
¿Por qué declararlas en la base de datos y no limitarse a validar los datos en la aplicación? Porque la base de datos es compartida: la usan varias aplicaciones, scripts de carga, herramientas de informes y personas con SQL Developer. Una regla programada solo en la web no protege de un UPDATE lanzado desde otro programa. Además, las restricciones documentan el modelo y permiten al optimizador hacer suposiciones que aceleran las consultas.
💡 Consejo: Valida en la aplicación para dar buenos mensajes al usuario; garantiza en la base de datos. Las dos validaciones se complementan.
📘 Concepto: Las salidas de este módulo se han obtenido con la configuración de sesión del módulo 22 (
SET NULL '(nulo)', idioma y territorio españoles yNLS_DATE_FORMAT = 'DD/MM/YYYY'). Para no alargar el texto, en los bloques con varios errores seguidos se muestran solo las líneasERROR en líneayORA-de cada uno, y se omite la líneaHelp:que Oracle añade al final de cada error. Las tablas..._demoson de usar y tirar: bórralas al terminar cada apartado conDROP TABLE nombre PURGE;.
2. Definición en línea y fuera de línea
Hay dos lugares donde escribir una restricción dentro de CREATE TABLE:
- En línea (inline constraint o restricción de columna): junto a la definición de la columna, sin coma de separación. Solo afecta a esa columna.
- Fuera de línea (out-of-line constraint o restricción de tabla): como un elemento más de la lista, separado por comas, normalmente al final. Indica entre paréntesis las columnas a las que afecta.
CREATE TABLE alumnos_demo (
-- En línea: la restricción va pegada a la columna
alumno_id NUMBER(6) CONSTRAINT pk_alumnos_demo PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_alu_nombre NOT NULL,
email VARCHAR2(60),
edad NUMBER(3),
-- Fuera de línea: elementos de la lista, con las columnas entre paréntesis
CONSTRAINT uq_alu_email UNIQUE (email),
CONSTRAINT ck_alu_edad CHECK (edad >= 16)
);
| Aspecto | En línea | Fuera de línea |
|---|---|---|
| Sintaxis | col tipo [CONSTRAINT nombre] tipo_restricción | [CONSTRAINT nombre] tipo_restricción (columnas) |
NOT NULL | Solo en línea | No permitido (se usaría un CHECK (col IS NOT NULL)) |
Restricciones de varias columnas (PK o FK compuesta, CHECK que compara columnas) | No permitido | Obligatorio |
| Legibilidad | Muy clara para restricciones simples | Agrupa todas las reglas al final de la tabla |
NOT NULL fuera de línea no es válido:
CREATE TABLE alumnos_demo (
alumno_id NUMBER(6),
nombre VARCHAR2(40),
CONSTRAINT nn_alu_nombre NOT NULL (nombre)
);
CONSTRAINT nn_alu_nombre NOT NULL (nombre)
*
ERROR en línea 4:
ORA-03050: invalid identifier: "NOT" is a reserved word
Y un CHECK en línea no puede mencionar otras columnas:
CREATE TABLE ck_otra_columna (a NUMBER CONSTRAINT ck_a CHECK (a < b), b NUMBER);
CREATE TABLE ck_otra_columna (a NUMBER CONSTRAINT ck_a CHECK (a < b), b NUMBER)
*
ERROR en línea 1:
ORA-02438: Column check constraint cannot reference other columns
La versión fuera de línea, CONSTRAINT ck_a CHECK (a < b) al final de la lista, funciona.
💡 Consejo: Un estilo muy extendido (el de los scripts de EMPRESA) es:
NOT NULLy la clave primaria simple en línea;UNIQUE,CHECKyFOREIGN KEYfuera de línea, agrupadas al final. Lo importante es ser coherente en todo el script.
También se pueden añadir restricciones a una tabla ya creada con ALTER TABLE ... ADD CONSTRAINT (lo verás a fondo en el módulo 25 y lo usaremos en la sección 12):
ALTER TABLE alumnos_demo ADD CONSTRAINT ck_alu_email CHECK (email LIKE '%@%');
3. Nombres de restricciones
Cada restricción tiene un nombre único dentro del esquema. Si no lo das con la palabra CONSTRAINT, Oracle genera uno con la forma SYS_Cnnnnnn:
CREATE TABLE alumnos_demo (
alumno_id NUMBER(6) PRIMARY KEY,
nombre VARCHAR2(40) NOT NULL,
email VARCHAR2(60) UNIQUE,
edad NUMBER(3) CHECK (edad >= 16)
);
COLUMN constraint_name FORMAT A22
COLUMN search_condition_vc FORMAT A40
SELECT constraint_name, constraint_type, search_condition_vc
FROM user_constraints
WHERE table_name = 'ALUMNOS_DEMO'
ORDER BY constraint_name;
CONSTRAINT_NAME C SEARCH_CONDITION_VC
---------------------- - ----------------------------------------
SYS_C009622 C "NOMBRE" IS NOT NULL
SYS_C009623 C edad >= 16
SYS_C009624 P (nulo)
SYS_C009625 U (nulo)
4 filas seleccionadas.
¿Qué ocurre cuando alguien incumple una de ellas?
INSERT INTO alumnos_demo VALUES (1, 'Ana', 'ana@correo.es', 15);
INSERT INTO alumnos_demo VALUES (1, 'Ana', 'ana@correo.es', 15)
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.SYS_C009623) violated
SYS_C009623 no dice nada: hay que ir al diccionario para averiguar qué regla es. Con nombres propios, el mismo error se entiende a la primera:
CREATE TABLE alumnos_demo (
alumno_id NUMBER(6) CONSTRAINT pk_alumnos_demo PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_alu_nombre NOT NULL,
email VARCHAR2(60) CONSTRAINT uq_alu_email UNIQUE,
edad NUMBER(3) CONSTRAINT ck_alu_edad CHECK (edad >= 16)
);
INSERT INTO alumnos_demo VALUES (1, 'Ana', 'ana@correo.es', 15);
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_ALU_EDAD) violated
Además, para desactivar, borrar o modificar una restricción (módulo 25) hay que nombrarla, y los nombres SYS_C cambian de una base de datos a otra, lo que impide escribir scripts de mantenimiento que funcionen en todas.
⚠️ Importante: Nombra siempre tus restricciones. La convención del curso es
prefijo_tabla_columna, con la tabla abreviada a tres letras:pk_(clave primaria, con el nombre completo de la tabla),uq_(única),fk_(ajena),ck_(check) ynn_(not null). Recuerda el límite de 128 bytes del nombre.
4. NOT NULL
📘 Concepto:
NOT NULLobliga a que la columna tenga siempre un valor. Implementa los atributos obligatorios del diseño, es decir, aquellos con cardinalidad mínima 1.
CREATE TABLE alumnos_demo (
alumno_id NUMBER(6) CONSTRAINT pk_alumnos_demo PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_alu_nombre NOT NULL,
telefono VARCHAR2(15)
);
INSERT INTO alumnos_demo (alumno_id, telefono) VALUES (1, '600000000'); -- falta el nombre
INSERT INTO alumnos_demo VALUES (2, '', NULL); -- cadena vacía
INSERT INTO alumnos_demo VALUES (3, ' ', NULL); -- un espacio
INSERT INTO alumnos_demo (alumno_id, telefono) VALUES (1, '600000000')
*
ERROR en línea 1:
ORA-01400: cannot insert NULL into ("CURSO"."ALUMNOS_DEMO"."NOMBRE")
INSERT INTO alumnos_demo VALUES (2, '', NULL)
*
ERROR en línea 1:
ORA-01400: cannot insert NULL into ("CURSO"."ALUMNOS_DEMO"."NOMBRE")
1 fila creada.
Tres observaciones importantes:
- Omitir la columna en el
INSERTequivale a insertarNULL(salvo que tengaDEFAULT). - La cadena vacía
''esNULLen Oracle (módulo 23), así que también se rechaza. - Un espacio
' 'no es nulo y se acepta. Si quieres impedir textos en blanco, añadeCHECK (TRIM(nombre) IS NOT NULL).
Al intentar modificar a nulo una columna NOT NULL el error es otro, ORA-01407:
UPDATE lineas_pedido SET descuento = NULL WHERE pedido_id = 5001 AND num_linea = 1;
UPDATE lineas_pedido SET descuento = NULL WHERE pedido_id = 5001 AND num_linea = 1
*
ERROR en línea 1:
ORA-01407: cannot update ("CURSO"."LINEAS_PEDIDO"."DESCUENTO") to NULL
📘 Concepto: Internamente, Oracle guarda
NOT NULLcomo una restricciónCHECK(tipoC) con la condición"COLUMNA" IS NOT NULL. Por eso en las consultas al diccionario las verás mezcladas con losCHECK.
5. PRIMARY KEY
📘 Concepto: La clave primaria (
PRIMARY KEY) identifica de forma única cada fila de la tabla. Implementa la integridad de entidad: sus columnas no pueden repetir la combinación de valores ni contener nulos. Cada tabla puede tener una sola clave primaria (simple o compuesta).
5.1. Clave primaria simple
Usando la tabla alumnos_demo anterior (que tiene el alumno 3):
INSERT INTO alumnos_demo VALUES (3, 'Luis', NULL); -- clave repetida
INSERT INTO alumnos_demo VALUES (NULL, 'Luis', NULL); -- clave nula
INSERT INTO alumnos_demo VALUES (3, 'Luis', NULL)
*
ERROR en línea 1:
ORA-00001: unique constraint (CURSO.PK_ALUMNOS_DEMO) violated on table CURSO.ALUMNOS_DEMO columns (ALUMNO_ID)
ORA-03301: (ORA-00001 details) row with column values (ALUMNO_ID:3) already exists
INSERT INTO alumnos_demo VALUES (NULL, 'Luis', NULL)
*
ERROR en línea 1:
ORA-01400: cannot insert NULL into ("CURSO"."ALUMNOS_DEMO"."ALUMNO_ID")
Fíjate en que la violación de una clave primaria se notifica con el mismo código que la de una clave única (ORA-00001: unique constraint): para Oracle, una clave primaria es una clave única no nula. Desde 23ai el mensaje añade la tabla, las columnas y los valores repetidos, lo que facilita mucho la depuración.
Intentar dos claves primarias da error:
CREATE TABLE dos_pk (a NUMBER PRIMARY KEY, b NUMBER PRIMARY KEY);
CREATE TABLE dos_pk (a NUMBER PRIMARY KEY, b NUMBER PRIMARY KEY)
*
ERROR en línea 1:
ORA-02260: table can have only one primary key
5.2. Clave primaria compuesta
Cuando la clave está formada por varias columnas, la restricción debe escribirse fuera de línea:
CREATE TABLE matriculas_demo (
alumno_id NUMBER(6),
asignatura_id NUMBER(4),
curso VARCHAR2(9),
nota NUMBER(4,2),
CONSTRAINT pk_matriculas_demo PRIMARY KEY (alumno_id, asignatura_id, curso)
);
INSERT INTO matriculas_demo VALUES (1, 10, '2025-2026', 7.5);
INSERT INTO matriculas_demo VALUES (1, 10, '2026-2027', NULL); -- distinto curso: correcto
INSERT INTO matriculas_demo VALUES (1, 10, '2025-2026', 9); -- combinación repetida
INSERT INTO matriculas_demo VALUES (1, NULL, '2025-2026', 9); -- una parte nula
INSERT INTO matriculas_demo VALUES (1, 10, '2025-2026', 9)
*
ERROR en línea 1:
ORA-00001: unique constraint (CURSO.PK_MATRICULAS_DEMO) violated on table CURSO.MATRICULAS_DEMO columns (ALUMNO_ID, ASIGNATURA_ID, CURSO)
ORA-03301: (ORA-00001 details) row with column values (ALUMNO_ID:1, ASIGNATURA_ID:10, CURSO:'2025-2026') already exists
INSERT INTO matriculas_demo VALUES (1, NULL, '2025-2026', 9)
*
ERROR en línea 1:
ORA-01400: cannot insert NULL into ("CURSO"."MATRICULAS_DEMO"."ASIGNATURA_ID")
Lo que debe ser único es la combinación: el alumno 1 puede repetir asignatura en otro curso. Ninguna de las columnas de la clave admite nulos, y DESCRIBE las muestra como NOT NULL aunque no lo hayas escrito:
Nombre ¿Nulo? Tipo
----------------------------------------- -------- ----------------------------
ALUMNO_ID NOT NULL NUMBER(6)
ASIGNATURA_ID NOT NULL NUMBER(4)
CURSO NOT NULL VARCHAR2(9)
NOTA NUMBER(4,2)
El orden de las columnas en la clave se guarda en USER_CONS_COLUMNS:
SELECT constraint_name, column_name, position
FROM user_cons_columns
WHERE table_name = 'MATRICULAS_DEMO'
ORDER BY constraint_name, position;
CONSTRAINT_NAME COLUMN_NAME POSITION
---------------------- -------------- ----------
PK_MATRICULAS_DEMO ALUMNO_ID 1
PK_MATRICULAS_DEMO ASIGNATURA_ID 2
PK_MATRICULAS_DEMO CURSO 3
3 filas seleccionadas.
5.3. El índice asociado
Para comprobar rápidamente que no hay duplicados, Oracle crea automáticamente un índice único con el mismo nombre que la restricción:
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'MATRICULAS_DEMO';
INDEX_NAME UNIQUENES
---------------------- ---------
PK_MATRICULAS_DEMO UNIQUE
1 fila seleccionada.
Esos son los 14 índices que encontraste en USER_OBJECTS del esquema EMPRESA (9 claves primarias y 5 únicas). El índice además acelera las búsquedas por la clave. Lo estudiarás en el módulo 26.
💡 Consejo: ¿Clave natural o sustituta? Una clave natural (DNI, ISBN, matrícula) tiene significado fuera de la base de datos; una sustituta (
cliente_idnumérico, identidad o secuencia) no. Las sustitutas son compactas, nunca cambian y no dependen de reglas externas (el DNI de un extranjero puede cambiar a NIE, un ISBN puede reutilizarse). La práctica habitual es usar una sustituta como PK y declarar la natural comoUNIQUE NOT NULL, como hace EMPRESA concliente_idynif.
6. UNIQUE
📘 Concepto: La restricción
UNIQUEimpide que se repitan valores (o combinaciones de valores) en una o varias columnas. Implementa las claves alternativas. A diferencia de la clave primaria, admite nulos y una tabla puede tener todas las que necesite.
6.1. UNIQUE y los valores nulos
El comportamiento con nulos es la parte delicada:
CREATE TABLE socios_demo (
socio_id NUMBER(6) CONSTRAINT pk_socios_demo PRIMARY KEY,
dni VARCHAR2(9) CONSTRAINT uq_soc_dni UNIQUE,
pais VARCHAR2(2),
num_pasaporte VARCHAR2(12),
CONSTRAINT uq_soc_pasaporte UNIQUE (pais, num_pasaporte)
);
INSERT INTO socios_demo VALUES (1, '11111111H', NULL, NULL);
INSERT INTO socios_demo VALUES (2, '11111111H', NULL, NULL); -- DNI repetido
INSERT INTO socios_demo VALUES (3, NULL, NULL, NULL); -- DNI nulo
INSERT INTO socios_demo VALUES (4, NULL, NULL, NULL); -- otro DNI nulo
INSERT INTO socios_demo VALUES (5, NULL, 'ES', 'PAA123456');
INSERT INTO socios_demo VALUES (6, NULL, 'FR', 'PAA123456'); -- mismo pasaporte, otro país
INSERT INTO socios_demo VALUES (7, NULL, 'ES', NULL); -- pasaporte nulo
INSERT INTO socios_demo VALUES (8, NULL, 'ES', NULL); -- otra vez ES + nulo
INSERT INTO socios_demo VALUES (2, '11111111H', NULL, NULL)
*
ERROR en línea 1:
ORA-00001: unique constraint (CURSO.UQ_SOC_DNI) violated on table CURSO.SOCIOS_DEMO columns (DNI)
ORA-03301: (ORA-00001 details) row with column values (DNI:'11111111H') already exists
INSERT INTO socios_demo VALUES (8, NULL, 'ES', NULL)
*
ERROR en línea 1:
ORA-00001: unique constraint (CURSO.UQ_SOC_PASAPORTE) violated on table CURSO.SOCIOS_DEMO columns (PAIS, NUM_PASAPORTE)
ORA-03301: (ORA-00001 details) row with column values (PAIS:'ES', NUM_PASAPORTE:NULL) already exists
SELECT socio_id, dni, pais, num_pasaporte FROM socios_demo ORDER BY socio_id;
SOCIO_ID DNI PAIS NUM_PASAPORTE
---------- --------- ------ -------------
1 11111111H (nulo) (nulo)
3 (nulo) (nulo) (nulo)
4 (nulo) (nulo) (nulo)
5 (nulo) ES PAA123456
6 (nulo) FR PAA123456
7 (nulo) ES (nulo)
6 filas seleccionadas.
Las reglas de Oracle, por tanto, son:
| Caso | ¿Se considera duplicado? |
|---|---|
UNIQUE de una columna, varios NULL | No: se admiten tantos nulos como se quiera |
UNIQUE compuesto, todas las columnas NULL | No |
| UNIQUE compuesto, alguna columna nula y el resto iguales | Sí (filas 7 y 8: ('ES', NULL) dos veces) |
| UNIQUE compuesto, sin nulos | Duplicado si coinciden todas |
⚠️ Importante: El tercer caso es una particularidad de Oracle (otros SGBD, como PostgreSQL, admiten esas filas). SQL:2023 añadió las cláusulas
UNIQUE NULLS DISTINCT/NULLS NOT DISTINCTpara elegir el comportamiento, pero Oracle no las admite: aplica siempre la regla de la tabla anterior.
6.2. Clave alternativa completa: UNIQUE + NOT NULL
Una clave candidata del diseño no admite nulos, así que se implementa con UNIQUE y NOT NULL juntas. Es el caso de nif en CLIENTES y de email en EMPLEADOS:
nif VARCHAR2(9) CONSTRAINT nn_cli_nif NOT NULL,
...
CONSTRAINT uq_cli_nif UNIQUE (nif),
Si el atributo es opcional pero, cuando existe, único (por ejemplo, el correo electrónico de un cliente), basta con UNIQUE.
Igual que la clave primaria, cada restricción UNIQUE crea su propio índice único (UQ_SOC_DNI, UQ_SOC_PASAPORTE).
7. FOREIGN KEY
📘 Concepto: Una clave ajena o foránea (
FOREIGN KEY) es una columna (o conjunto de columnas) cuyos valores deben existir en la clave primaria o en una clave única de otra tabla (la tabla padre o referenciada), o bien ser nulos. Implementa la integridad referencial y es la forma en que el modelo relacional representa las relaciones del E/R.
7.1. Sintaxis
-- En línea (una sola columna): basta con REFERENCES
columna tipo [CONSTRAINT nombre] REFERENCES tabla_padre [(columna_padre)] [ON DELETE CASCADE | ON DELETE SET NULL]
-- Fuera de línea (una o varias columnas)
[CONSTRAINT nombre] FOREIGN KEY (col1 [, col2...])
REFERENCES tabla_padre [(colp1 [, colp2...])] [ON DELETE CASCADE | ON DELETE SET NULL]
Si no indicas las columnas del padre, se usa su clave primaria.
CREATE TABLE autores_demo (
autor_id NUMBER(5) CONSTRAINT pk_autores_demo PRIMARY KEY,
nombre VARCHAR2(50) CONSTRAINT nn_aut_nombre NOT NULL,
nif VARCHAR2(9) CONSTRAINT uq_aut_nif UNIQUE
);
CREATE TABLE libros_demo (
libro_id NUMBER(6) CONSTRAINT pk_libros_demo PRIMARY KEY,
titulo VARCHAR2(80) CONSTRAINT nn_lib_titulo NOT NULL,
autor_id NUMBER(5) CONSTRAINT fk_lib_autor REFERENCES autores_demo
);
7.2. Requisitos de la clave ajena
- La tabla padre debe existir antes: por eso los scripts crean primero las tablas padre y después las hijas.
- Las columnas referenciadas deben tener una restricción
PRIMARY KEYoUNIQUE:
CREATE TABLE fk_a_no_clave (x VARCHAR2(50) CONSTRAINT fk_mala REFERENCES autores_demo (nombre));
CREATE TABLE fk_a_no_clave (x VARCHAR2(50) CONSTRAINT fk_mala REFERENCES autores_demo (nombre))
*
ERROR en línea 1:
ORA-02270: no matching unique or primary key for this column-list
En cambio, REFERENCES autores_demo (nif) funciona, porque nif es UNIQUE.
- Los tipos deben ser compatibles (no hace falta que la longitud sea idéntica):
CREATE TABLE fk_tipo_distinto (x VARCHAR2(5) CONSTRAINT fk_tipo REFERENCES autores_demo (autor_id));
CREATE TABLE fk_tipo_distinto (x VARCHAR2(5) CONSTRAINT fk_tipo REFERENCES autores_demo (autor_id))
*
ERROR en línea 1:
ORA-02267: column type incompatible with referenced column type
💡 Consejo: Aunque Oracle admite, por ejemplo, una FK
NUMBER(9)que apunta a una PKNUMBER(5), declara la clave ajena con exactamente el mismo tipo que la columna referenciada: así no podrás intentar guardar valores que nunca podrían existir en el padre.
7.3. Qué comprueba la clave ajena
SELECT constraint_name, constraint_type, r_constraint_name, delete_rule
FROM user_constraints
WHERE table_name = 'LIBROS_DEMO'
ORDER BY 1;
CONSTRAINT_NAME C R_CONSTRAINT_NAME DELETE_RUL
---------------------- - ---------------------- ----------
FK_LIB_AUTOR R PK_AUTORES_DEMO NO ACTION
NN_LIB_TITULO C (nulo) (nulo)
PK_LIBROS_DEMO P (nulo) (nulo)
3 filas seleccionadas.
La FK (tipo R, de referential) apunta a la restricción PK_AUTORES_DEMO y su regla de borrado es NO ACTION. Veamos qué permite y qué no:
INSERT INTO autores_demo VALUES (1, 'Isabel Allende', NULL);
INSERT INTO libros_demo VALUES (1, 'La casa de los espíritus', 1); -- el autor 1 existe
INSERT INTO libros_demo VALUES (2, 'Libro anónimo', NULL); -- FK nula: permitido
INSERT INTO libros_demo VALUES (3, 'Libro huérfano', 99); -- el autor 99 no existe
INSERT INTO libros_demo VALUES (3, 'Libro huérfano', 99)
*
ERROR en línea 1:
ORA-02291: integrity constraint (CURSO.FK_LIB_AUTOR) violated - parent key not found
DELETE FROM autores_demo WHERE autor_id = 1; -- tiene libros
DELETE FROM autores_demo WHERE autor_id = 1
*
ERROR en línea 1:
ORA-02292: integrity constraint (CURSO.FK_LIB_AUTOR) violated - child record found
Los dos errores de la integridad referencial que verás constantemente:
| Error | Significado | Se produce al… |
|---|---|---|
ORA-02291 ... parent key not found | El valor de la FK no existe en el padre (se crearía un huérfano) | INSERT o UPDATE en la tabla hija |
ORA-02292 ... child record found | La fila del padre tiene hijos que se quedarían huérfanos | DELETE o UPDATE de la clave en la tabla padre |
Lo mismo ocurre en EMPRESA:
-- Un empleado en un departamento que no existe
INSERT INTO empleados VALUES (200, 'Prueba', 'Sin Depto', 'prueba@empresa.es', NULL,
DATE '2026-10-01', 'BECARIO', 900, NULL, 100, 99);
-- Borrar un departamento con empleados y proyectos
DELETE FROM departamentos WHERE dept_id = 30;
-- Cambiar la clave de un departamento con empleados
UPDATE departamentos SET dept_id = 31 WHERE dept_id = 30;
ERROR en línea 1:
ORA-02291: integrity constraint (CURSO.FK_EMP_DEPT) violated - parent key not found
ERROR en línea 1:
ORA-02292: integrity constraint (CURSO.FK_PRY_DEPT) violated - child record found
ERROR en línea 1:
ORA-02292: integrity constraint (CURSO.FK_EMP_DEPT) violated - child record found
Al borrar el departamento 30, la primera restricción que salta es la de PROYECTOS, pero también lo habría impedido la de EMPLEADOS. En cambio, DELETE FROM departamentos WHERE dept_id = 70; sí funciona, porque INVESTIGACIÓN no tiene empleados ni proyectos (haz ROLLBACK después).
Tampoco se puede borrar una tabla padre mientras otras tablas la referencien:
DROP TABLE departamentos;
DROP TABLE departamentos
*
ERROR en línea 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Con DROP TABLE ... CASCADE CONSTRAINTS Oracle borra la tabla y las claves ajenas que apuntan a ella (las tablas hijas se quedan, sin la FK). Es lo que hace empresa_borrar.sql.
⚠️ Importante: Oracle no crea automáticamente un índice sobre las columnas de una clave ajena (sí lo hace para PK y UNIQUE). Sin ese índice, cada
DELETEen el padre obliga a recorrer toda la tabla hija y puede provocar bloqueos de toda la tabla. En tablas grandes, indexa las claves ajenas (módulo 26).
7.4. Claves ajenas compuestas
Si la clave del padre es compuesta, la FK también lo es y se escribe fuera de línea, con las columnas en el mismo orden:
CREATE TABLE asignaturas_demo (
titulacion VARCHAR2(5),
codigo NUMBER(3),
nombre VARCHAR2(60) CONSTRAINT nn_asd_nombre NOT NULL,
CONSTRAINT pk_asignaturas_demo PRIMARY KEY (titulacion, codigo)
);
CREATE TABLE grupos_demo (
grupo_id NUMBER(5) CONSTRAINT pk_grupos_demo PRIMARY KEY,
titulacion VARCHAR2(5),
codigo NUMBER(3),
CONSTRAINT fk_gru_asignatura FOREIGN KEY (titulacion, codigo)
REFERENCES asignaturas_demo (titulacion, codigo)
);
INSERT INTO asignaturas_demo VALUES ('DAM', 101, 'Bases de Datos');
INSERT INTO grupos_demo VALUES (1, 'DAM', 101); -- existe
INSERT INTO grupos_demo VALUES (2, 'DAM', 999); -- no existe
INSERT INTO grupos_demo VALUES (3, 'DAM', NULL); -- ¿?
INSERT INTO grupos_demo VALUES (4, 'XXX', NULL); -- ¿¿??
INSERT INTO grupos_demo VALUES (2, 'DAM', 999)
*
ERROR en línea 1:
ORA-02291: integrity constraint (CURSO.FK_GRU_ASIGNATURA) violated - parent key not found
SELECT * FROM grupos_demo;
GRUPO_ID TITULACION CODIGO
---------- ---------- ----------
1 DAM 101
3 DAM (nulo)
4 XXX (nulo)
3 filas seleccionadas.
⚠️ Importante: En una FK compuesta, si cualquiera de sus columnas es nula, Oracle no comprueba el resto (regla MATCH SIMPLE del estándar). Por eso se ha aceptado la titulación
'XXX', que no existe. Si la relación es opcional, añade unCHECKque obligue a que las columnas sean todas nulas o todas no nulas:
CONSTRAINT ck_gru_asignatura CHECK ((titulacion IS NULL AND codigo IS NULL) OR (titulacion IS NOT NULL AND codigo IS NOT NULL))Y si es obligatoria, declara todas las columnas
NOT NULL.
7.5. Autorreferencia
Una FK puede apuntar a la propia tabla: así se implementan las relaciones reflexivas (jerarquías), como jefe_id en EMPLEADOS o categoria_padre_id en CATEGORIAS.
CREATE TABLE trabajadores_demo (
trab_id NUMBER(4) CONSTRAINT pk_trabajadores_demo PRIMARY KEY,
nombre VARCHAR2(30) CONSTRAINT nn_tra_nombre NOT NULL,
jefe_id NUMBER(4) CONSTRAINT fk_tra_jefe REFERENCES trabajadores_demo (trab_id),
CONSTRAINT ck_tra_jefe CHECK (jefe_id <> trab_id)
);
INSERT INTO trabajadores_demo VALUES (1, 'Directora', NULL); -- la raíz no tiene jefe
INSERT INTO trabajadores_demo VALUES (2, 'Jefe de equipo', 1);
INSERT INTO trabajadores_demo VALUES (3, 'Programadora', 2);
INSERT INTO trabajadores_demo VALUES (4, 'Él mismo', 4); -- su propio jefe
INSERT INTO trabajadores_demo VALUES (5, 'Jefe inexistente', 50);
DELETE FROM trabajadores_demo WHERE trab_id = 2; -- tiene subordinados
INSERT INTO trabajadores_demo VALUES (4, 'Él mismo', 4)
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_TRA_JEFE) violated
INSERT INTO trabajadores_demo VALUES (5, 'Jefe inexistente', 50)
*
ERROR en línea 1:
ORA-02291: integrity constraint (CURSO.FK_TRA_JEFE) violated - parent key not found
DELETE FROM trabajadores_demo WHERE trab_id = 2
*
ERROR en línea 1:
ORA-02292: integrity constraint (CURSO.FK_TRA_JEFE) violated - child record found
Claves de la autorreferencia: la columna debe admitir nulos (alguien está en la cima), las filas se insertan de arriba abajo (primero los jefes, como en empresa_datos.sql) y el CHECK evita el ciclo más simple. Los ciclos largos (A jefe de B, B jefe de A) no se pueden impedir de forma declarativa.
8. Acciones referenciales
¿Qué debe ocurrir con los hijos cuando se borra una fila del padre? La cláusula ON DELETE lo decide:
| Acción | Efecto al borrar la fila padre | DELETE_RULE | Cuándo usarla |
|---|---|---|---|
| (ninguna) = NO ACTION | Se rechaza el borrado si tiene hijos (ORA-02292) | NO ACTION | Por defecto. Los hijos tienen entidad propia (clientes de un comercial, pedidos de un cliente) |
ON DELETE CASCADE | Se borran también los hijos (y los hijos de los hijos, si sus FK también son en cascada) | CASCADE | Los hijos no tienen sentido sin el padre: entidades débiles, líneas de un pedido, tablas N:M |
ON DELETE SET NULL | La FK de los hijos se pone a NULL | SET NULL | Relaciones opcionales donde el hijo sobrevive (jugadores de un club que desaparece) |
CREATE TABLE clubes_demo (
club_id NUMBER(4) CONSTRAINT pk_clubes_demo PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_clu_nombre NOT NULL
);
CREATE TABLE jugadores_demo (
jugador_id NUMBER(6) CONSTRAINT pk_jugadores_demo PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_jug_nombre NOT NULL,
club_id NUMBER(4) CONSTRAINT fk_jug_club REFERENCES clubes_demo (club_id)
ON DELETE SET NULL
);
CREATE TABLE cuotas_demo (
club_id NUMBER(4) CONSTRAINT fk_cuo_club REFERENCES clubes_demo (club_id)
ON DELETE CASCADE,
anio NUMBER(4),
importe NUMBER(8,2),
CONSTRAINT pk_cuotas_demo PRIMARY KEY (club_id, anio)
);
INSERT INTO clubes_demo VALUES (1, 'Valencia Basket');
INSERT INTO clubes_demo VALUES (2, 'Unicaja');
INSERT INTO jugadores_demo VALUES (10, 'Jugador A', 1);
INSERT INTO jugadores_demo VALUES (11, 'Jugador B', 1);
INSERT INTO jugadores_demo VALUES (12, 'Jugador C', 2);
INSERT INTO cuotas_demo VALUES (1, 2025, 1200);
INSERT INTO cuotas_demo VALUES (1, 2026, 1300);
INSERT INTO cuotas_demo VALUES (2, 2026, 1100);
DELETE FROM clubes_demo WHERE club_id = 1;
1 fila suprimida.
Un único DELETE ha tenido tres efectos:
SELECT * FROM jugadores_demo ORDER BY jugador_id;
SELECT * FROM cuotas_demo ORDER BY club_id, anio;
JUGADOR_ID NOMBRE CLUB_ID
---------- -------------------- ----------
10 Jugador A (nulo)
11 Jugador B (nulo)
12 Jugador C 2
3 filas seleccionadas.
CLUB_ID ANIO IMPORTE
---------- ---------- ----------
2 2026 1100
1 fila seleccionada.
Los jugadores del club 1 siguen existiendo, pero sin club; las cuotas del club 1 han desaparecido. En EMPRESA hay dos FK en cascada: fk_lin_pedido (borrar un pedido borra sus líneas) y fk_asi_proyecto (borrar un proyecto borra sus asignaciones):
DELETE FROM pedidos WHERE pedido_id = 5001;
SELECT COUNT(*) FROM lineas_pedido WHERE pedido_id = 5001;
ROLLBACK;
SELECT COUNT(*) FROM lineas_pedido WHERE pedido_id = 5001;
1 fila suprimida.
COUNT(*)
----------
0
1 fila seleccionada.
Rollback terminado.
COUNT(*)
----------
3
1 fila seleccionada.
⚠️ Importante:
ON DELETE SET NULLsobre una columnaNOT NULLes una contradicción que Oracle no detecta al crear la tabla, sino al borrar: el borrado falla conORA-01407: cannot update ("CURSO"."HIJO_DEMO"."PADRE_ID") to NULL. YON DELETE CASCADEes potente pero peligroso: unDELETEdescuidado en la tabla padre puede arrastrar miles de filas. Úsalo solo cuando el hijo sea realmente dependiente del padre.
8.1. ¿Y ON UPDATE CASCADE?
El estándar SQL define también ON UPDATE CASCADE | SET NULL | SET DEFAULT y ON DELETE SET DEFAULT, pero Oracle no los implementa:
CREATE TABLE con_update (x NUMBER REFERENCES clubes_demo ON UPDATE CASCADE);
CREATE TABLE con_update (x NUMBER REFERENCES clubes_demo ON UPDATE CASCADE)
*
ERROR en línea 1:
ORA-02000: missing DELETE keyword
En Oracle, actualizar una clave primaria referenciada siempre se comporta como NO ACTION. Alternativas:
- La mejor: no actualizar nunca las claves primarias. Usa claves sustitutas (números sin significado) que nunca necesitan cambiar. Si cambia el NIF de un cliente, cambias la columna
nif(que esUNIQUE), nocliente_id. - Insertar–mover–borrar: insertar la fila padre con la clave nueva, actualizar los hijos para que apunten a ella y borrar la fila antigua, todo en la misma transacción.
- Restricciones diferibles (sección 12): con la FK diferida, puedes actualizar padre e hijos en cualquier orden dentro de la transacción; la comprobación se hace en el
COMMIT. - Disparadores (triggers) que propaguen el cambio: requieren PL/SQL y se estudian en el Curso de Oracle PL/SQL.
9. CHECK
📘 Concepto: Una restricción
CHECKes una condición que debe cumplir cada fila. Implementa las restricciones de dominio (valores permitidos de un atributo) y las reglas de negocio que se pueden expresar mirando solo los valores de la propia fila.
9.1. Condiciones de una y varias columnas
CREATE TABLE reservas_demo (
reserva_id NUMBER(6) CONSTRAINT pk_reservas_demo PRIMARY KEY,
fecha_entrada DATE CONSTRAINT nn_res_entrada NOT NULL,
fecha_salida DATE CONSTRAINT nn_res_salida NOT NULL,
personas NUMBER(2) CONSTRAINT ck_res_personas CHECK (personas BETWEEN 1 AND 8),
regimen VARCHAR2(2) CONSTRAINT ck_res_regimen CHECK (regimen IN ('SA', 'AD', 'MP', 'PC')),
codigo VARCHAR2(8) CONSTRAINT ck_res_codigo CHECK (REGEXP_LIKE(codigo, '^R[0-9]{7}$')),
email VARCHAR2(60) CONSTRAINT ck_res_email CHECK (email LIKE '%_@_%._%'),
CONSTRAINT ck_res_fechas CHECK (fecha_salida > fecha_entrada)
);
INSERT INTO reservas_demo VALUES (1, DATE '2026-08-01', DATE '2026-08-08', 2, 'MP', 'R0000001', 'ana@correo.es');
INSERT INTO reservas_demo VALUES (2, DATE '2026-08-08', DATE '2026-08-01', 2, 'MP', 'R0000002', NULL);
INSERT INTO reservas_demo VALUES (3, DATE '2026-08-01', DATE '2026-08-08', 9, 'MP', 'R0000003', NULL);
INSERT INTO reservas_demo VALUES (4, DATE '2026-08-01', DATE '2026-08-08', 2, 'mp', 'R0000004', NULL);
INSERT INTO reservas_demo VALUES (5, DATE '2026-08-01', DATE '2026-08-08', 2, 'AD', 'X123', NULL);
INSERT INTO reservas_demo VALUES (6, DATE '2026-08-01', DATE '2026-08-08', 2, 'AD', 'R0000006', 'sin-arroba');
INSERT INTO reservas_demo VALUES (7, DATE '2026-08-01', DATE '2026-08-08', NULL, NULL, NULL, NULL);
1 fila creada.
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_RES_FECHAS) violated
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_RES_PERSONAS) violated
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_RES_REGIMEN) violated
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_RES_CODIGO) violated
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_RES_EMAIL) violated
1 fila creada.
Las condiciones pueden usar comparaciones, BETWEEN, IN, LIKE, IS [NOT] NULL, AND, OR, NOT, CASE y funciones deterministas (UPPER, LENGTH, TRIM, REGEXP_LIKE…). El CHECK de fechas compara dos columnas de la misma fila, por eso va fuera de línea.
9.2. CHECK y los valores nulos
¡La reserva 7 se ha aceptado con personas, regimen, codigo y email nulos!
SELECT reserva_id, personas, regimen, codigo FROM reservas_demo;
RESERVA_ID PERSONAS REGIMEN CODIGO
---------- ---------- ------- --------
1 2 MP R0000001
7 (nulo) (nulo) (nulo)
2 filas seleccionadas.
⚠️ Importante: Un
CHECKrechaza la fila solo si la condición es FALSA. Si es DESCONOCIDA (porque interviene unNULL), la fila se acepta.NULL BETWEEN 1 AND 8no es verdadero ni falso: es desconocido. Es el comportamiento contrario al deWHERE, que solo devuelve filas cuya condición es verdadera. Si el atributo es obligatorio, elCHECKdebe ir acompañado de unNOT NULL.
Este comportamiento es muy útil en EMPRESA: ck_ped_fechas CHECK (fecha_envio >= fecha_pedido) no impide insertar pedidos pendientes con fecha_envio nula, pero sí que se envíe antes de pedirse:
UPDATE pedidos SET fecha_envio = DATE '2024-12-31' WHERE pedido_id = 5001;
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_PED_FECHAS) violated
9.3. Limitaciones de CHECK
La condición se evalúa con los datos de una fila y debe dar siempre el mismo resultado para los mismos valores. Por eso no puede contener:
| No permitido | Ejemplo | Error |
|---|---|---|
Funciones no deterministas: SYSDATE, SYSTIMESTAMP, CURRENT_DATE | CHECK (f <= SYSDATE) | ORA-02436 |
Datos de la sesión: USER, UID, USERENV | CHECK (u = USER) | ORA-02436 |
| Subconsultas, ni por tanto referencias a otras tablas | CHECK (d IN (SELECT dept_id FROM departamentos)) | ORA-02251 |
Pseudocolumnas: ROWNUM, LEVEL, CURRVAL, NEXTVAL | CHECK (ROWNUM < 10) | ORA-00976 |
Funciones de agregado (COUNT, SUM…) y funciones PL/SQL de usuario |
CREATE TABLE ck_sysdate (f DATE CONSTRAINT ck_f CHECK (f <= SYSDATE));
CREATE TABLE ck_subconsulta (d NUMBER CONSTRAINT ck_d CHECK (d IN (SELECT dept_id FROM departamentos)));
CREATE TABLE ck_sysdate (f DATE CONSTRAINT ck_f CHECK (f <= SYSDATE))
*
ERROR en línea 1:
ORA-02436: date or system variable wrongly specified in CHECK constraint
CREATE TABLE ck_subconsulta (d NUMBER CONSTRAINT ck_d CHECK (d IN (SELECT dept_id FROM departamentos)))
*
ERROR en línea 1:
ORA-02251: subquery not allowed here
Por qué tiene sentido la prohibición de SYSDATE: una fila válida hoy (fecha_alta <= SYSDATE) podría no serlo mañana si la condición fuera la contraria, y la base de datos quedaría en un estado inconsistente sin que nadie hubiera modificado nada. En cambio, una fecha fija sí está permitida: CHECK (fecha_nacimiento >= DATE '1900-01-01'), igual que TO_DATE con formato completo.
Alternativas para las reglas que un CHECK no puede expresar:
| Regla | Alternativa |
|---|---|
| El valor debe existir en otra tabla | Clave ajena (tabla de códigos) |
Comparar con la fecha actual (fecha_alta <= SYSDATE) | Valor DEFAULT SYSDATE + disparador (PL/SQL) o validación en la aplicación |
| Regla entre filas o entre tablas | Aserción (26ai, sección 16.4), disparador (PL/SQL) o vista materializada con restricción (avanzado) |
10. DEFAULT no es una restricción
DEFAULT (módulo 23) suele aparecer en la misma línea que las restricciones, pero es otra cosa:
DEFAULT | Restricción (NOT NULL, CHECK…) | |
|---|---|---|
| Qué hace | Propone un valor cuando el INSERT no da ninguno | Prohíbe los valores que incumplen la regla |
| ¿Puede incumplirse? | Sí: se puede insertar otro valor o NULL explícito | No: la operación se rechaza |
¿Actúa en UPDATE? | No (salvo DEFAULT ON NULL FOR INSERT AND UPDATE, 23ai) | Sí |
¿Tiene nombre? ¿Aparece en USER_CONSTRAINTS? | No; está en USER_TAB_COLUMNS.DATA_DEFAULT | Sí |
| Orden en la definición de la columna | Va antes que las restricciones | Después del DEFAULT |
-- Correcto: primero DEFAULT, después las restricciones (como en EMPRESA)
estado VARCHAR2(12) DEFAULT 'PENDIENTE' CONSTRAINT nn_ped_estado NOT NULL,
Combinados son muy potentes: DEFAULT 'N' + NOT NULL + CHECK (... IN ('S','N')) en productos.descatalogado garantiza que la columna siempre tiene un valor válido sin obligar a escribirlo en cada INSERT.
11. Estados de las restricciones
Una restricción no está simplemente “activa” o “inactiva”. Oracle combina dos propiedades independientes:
ENABLE/DISABLE: ¿se comprueban los datos nuevos (losINSERTyUPDATEque se hagan a partir de ahora)?VALIDATE/NOVALIDATE: ¿se garantiza que los datos existentes la cumplen?
| Estado | Datos nuevos | Datos existentes | Uso típico |
|---|---|---|---|
ENABLE VALIDATE (por defecto) | Se comprueban | Se garantiza que cumplen | Funcionamiento normal |
ENABLE NOVALIDATE | Se comprueban | Pueden incumplirla | Activar una regla nueva sin depurar los datos históricos |
DISABLE NOVALIDATE (por defecto de DISABLE) | No se comprueban | Pueden incumplirla | Cargas masivas de datos rápidas |
DISABLE VALIDATE | Se prohíbe modificar la tabla | Se garantiza que cumplen | Tablas de solo lectura en almacenes de datos |
Al escribir ENABLE a secas se entiende ENABLE VALIDATE; y DISABLE a secas es DISABLE NOVALIDATE. El estado se cambia con ALTER TABLE ... ENABLE | DISABLE [VALIDATE | NOVALIDATE] CONSTRAINT nombre (o al crear la restricción, añadiendo el estado al final).
💻 Ejemplo: el ciclo completo con una restricción
CHECK:
CREATE TABLE notas_demo (
alumno VARCHAR2(20) CONSTRAINT pk_notas_demo PRIMARY KEY,
nota NUMBER(4,2) CONSTRAINT ck_not_nota CHECK (nota BETWEEN 0 AND 10)
);
INSERT INTO notas_demo VALUES ('Ana', 8);
COLUMN status FORMAT A8
COLUMN validated FORMAT A13
SELECT constraint_name, status, validated FROM user_constraints WHERE table_name = 'NOTAS_DEMO' ORDER BY 1;
CONSTRAINT_NAME STATUS VALIDATED
---------------------- -------- -------------
CK_NOT_NOTA ENABLED VALIDATED
PK_NOTAS_DEMO ENABLED VALIDATED
2 filas seleccionadas.
Desactivamos el CHECK y se cuela una nota imposible:
ALTER TABLE notas_demo DISABLE CONSTRAINT ck_not_nota;
INSERT INTO notas_demo VALUES ('Luis', 12);
SELECT constraint_name, status, validated FROM user_constraints WHERE table_name = 'NOTAS_DEMO' ORDER BY 1;
Tabla modificada.
1 fila creada.
CONSTRAINT_NAME STATUS VALIDATED
---------------------- -------- -------------
CK_NOT_NOTA DISABLED NOT VALIDATED
PK_NOTAS_DEMO ENABLED VALIDATED
2 filas seleccionadas.
Al intentar reactivarla, Oracle revisa los datos existentes y encuentra el 12:
ALTER TABLE notas_demo ENABLE CONSTRAINT ck_not_nota;
ALTER TABLE notas_demo ENABLE CONSTRAINT ck_not_nota
*
ERROR en línea 1:
ORA-02293: cannot validate (CURSO.CK_NOT_NOTA) - check constraint violated
Con NOVALIDATE se activa para los datos nuevos, dejando el 12 donde está:
ALTER TABLE notas_demo ENABLE NOVALIDATE CONSTRAINT ck_not_nota;
INSERT INTO notas_demo VALUES ('Marta', 11);
SELECT * FROM notas_demo;
Tabla modificada.
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_NOT_NOTA) violated
ALUMNO NOTA
-------------------- ----------
Ana 8
Luis 12
2 filas seleccionadas.
11.1. Encontrar las filas que incumplen: EXCEPTIONS INTO
Para validar hay que corregir antes los datos. ¿Cuáles? La cláusula EXCEPTIONS INTO guarda el ROWID de las filas culpables en una tabla de excepciones (su estructura la crea el script utlexcpt.sql de Oracle, o puedes crearla tú):
CREATE TABLE exceptions (
row_id ROWID,
owner VARCHAR2(128),
table_name VARCHAR2(128),
constraint VARCHAR2(128)
);
ALTER TABLE notas_demo ENABLE VALIDATE CONSTRAINT ck_not_nota EXCEPTIONS INTO exceptions;
ERROR en línea 1:
ORA-02293: cannot validate (CURSO.CK_NOT_NOTA) - check constraint violated
La sentencia falla, pero ha anotado los culpables:
SELECT n.* FROM notas_demo n WHERE ROWID IN (SELECT row_id FROM exceptions);
UPDATE notas_demo SET nota = 10 WHERE ROWID IN (SELECT row_id FROM exceptions);
ALTER TABLE notas_demo ENABLE VALIDATE CONSTRAINT ck_not_nota;
ALUMNO NOTA
-------------------- ----------
Luis 12
1 fila seleccionada.
1 fila actualizada.
Tabla modificada.
Con DISABLE VALIDATE la tabla queda protegida contra cualquier modificación:
ALTER TABLE notas_demo DISABLE VALIDATE CONSTRAINT ck_not_nota;
INSERT INTO notas_demo VALUES ('Pedro', 5);
ERROR en línea 1:
ORA-25128: No insert/update/delete on table with constraint (CURSO.CK_NOT_NOTA) disabled and validated
11.2. Desactivar claves primarias y ajenas
Al desactivar una PRIMARY KEY o UNIQUE, Oracle elimina su índice (si lo creó él), y entonces se pueden colar duplicados; al reactivarla, vuelve a crear el índice y valida:
ALTER TABLE notas_demo ENABLE CONSTRAINT ck_not_nota;
ALTER TABLE notas_demo DISABLE CONSTRAINT pk_notas_demo;
SELECT index_name FROM user_indexes WHERE table_name = 'NOTAS_DEMO';
INSERT INTO notas_demo VALUES ('Ana', 3);
ALTER TABLE notas_demo ENABLE CONSTRAINT pk_notas_demo;
ninguna fila seleccionada
1 fila creada.
ERROR en línea 1:
ORA-02437: cannot validate (CURSO.PK_NOTAS_DEMO) - primary key violated
Con las claves ajenas el error de validación es ORA-02298: cannot validate (...) - parent keys not found.
⚠️ Importante: No puedes desactivar una
PRIMARY KEYreferenciada por claves ajenas activas sin desactivarlas también (cláusulaDISABLE CONSTRAINT pk_x CASCADE). Y recuerda que toda sentencia DDL, tambiénALTER TABLE ... ENABLE, haceCOMMITantes de ejecutarse, aunque después falle. Mira este caso real:
ALTER TABLE empleados DISABLE CONSTRAINT fk_emp_dept;
INSERT INTO empleados VALUES (200, 'Prueba', 'Huérfana', 'prueba@empresa.es', NULL,
DATE '2026-10-01', 'BECARIO', 900, NULL, 100, 99);
ALTER TABLE empleados ENABLE CONSTRAINT fk_emp_dept; -- falla: hay un huérfano
ROLLBACK; -- ¿deshace el INSERT?
ALTER TABLE empleados ENABLE CONSTRAINT fk_emp_dept; -- sigue fallando
Tabla modificada.
1 fila creada.
ERROR en línea 1:
ORA-02298: cannot validate (CURSO.FK_EMP_DEPT) - parent keys not found
Rollback terminado.
ERROR en línea 1:
ORA-02298: cannot validate (CURSO.FK_EMP_DEPT) - parent keys not found
El ROLLBACK no deshizo nada: el primer ALTER TABLE confirmó el INSERT antes de fallar, y el empleado huérfano quedó guardado. Hubo que arreglarlo a mano:
DELETE FROM empleados WHERE emp_id = 200;
COMMIT;
ALTER TABLE empleados ENABLE CONSTRAINT fk_emp_dept;
1 fila suprimida.
Confirmación terminada.
Tabla modificada.
💡 Consejo: Desactivar restricciones solo está justificado en cargas masivas controladas (miles o millones de filas), y siempre seguido de una validación. Para el trabajo normal, déjalas siempre
ENABLE VALIDATE.
12. Restricciones diferibles
12.1. El problema: referencias circulares
Por defecto, cada restricción se comprueba al final de cada sentencia (modo inmediato). Esto hace imposible insertar datos que se referencian mutuamente. Imagina un club de equipos donde cada equipo tiene obligatoriamente un capitán, que es uno de sus miembros, y cada miembro pertenece obligatoriamente a un equipo. ¿Qué insertas primero? El equipo necesita un capitán que aún no existe; el miembro necesita un equipo que aún no existe.
12.2. DEFERRABLE e INITIALLY
📘 Concepto: Una restricción diferible (
DEFERRABLE) puede posponer su comprobación hasta elCOMMITde la transacción. Durante la transacción los datos pueden ser temporalmente inconsistentes; en elCOMMIT, si siguen incumpliendo la restricción, se deshace toda la transacción.
| Cláusula | Significado |
|---|---|
NOT DEFERRABLE (por defecto) | Siempre se comprueba al final de cada sentencia; no se puede cambiar |
DEFERRABLE INITIALLY IMMEDIATE | Se comprueba al final de cada sentencia, pero una transacción puede pedir que se difiera con SET CONSTRAINTS |
DEFERRABLE INITIALLY DEFERRED | Se comprueba en el COMMIT, salvo que se pida lo contrario |
El carácter DEFERRABLE se decide al crear la restricción y no se puede cambiar después (habría que borrarla y crearla de nuevo).
CREATE TABLE equipos_demo (
equipo_id NUMBER(4) CONSTRAINT pk_equipos_demo PRIMARY KEY,
nombre VARCHAR2(30) CONSTRAINT nn_equ_nombre NOT NULL,
capitan_id NUMBER(6) CONSTRAINT nn_equ_capitan NOT NULL
);
CREATE TABLE miembros_demo (
miembro_id NUMBER(6) CONSTRAINT pk_miembros_demo PRIMARY KEY,
nombre VARCHAR2(30) CONSTRAINT nn_mie_nombre NOT NULL,
equipo_id NUMBER(4) CONSTRAINT nn_mie_equipo NOT NULL,
CONSTRAINT fk_mie_equipo FOREIGN KEY (equipo_id) REFERENCES equipos_demo (equipo_id)
DEFERRABLE INITIALLY DEFERRED
);
-- La FK de equipos a miembros se añade después, cuando ya existe miembros_demo
ALTER TABLE equipos_demo ADD CONSTRAINT fk_equ_capitan
FOREIGN KEY (capitan_id) REFERENCES miembros_demo (miembro_id)
DEFERRABLE INITIALLY DEFERRED;
COLUMN deferrable FORMAT A14
COLUMN deferred FORMAT A9
SELECT constraint_name, deferrable, deferred
FROM user_constraints
WHERE table_name IN ('EQUIPOS_DEMO', 'MIEMBROS_DEMO') AND constraint_type = 'R';
CONSTRAINT_NAME DEFERRABLE DEFERRED
---------------------- -------------- ---------
FK_MIE_EQUIPO DEFERRABLE DEFERRED
FK_EQU_CAPITAN DEFERRABLE DEFERRED
2 filas seleccionadas.
Ahora la inserción circular funciona dentro de una transacción:
INSERT INTO equipos_demo VALUES (1, 'Los Ágiles', 100); -- el capitán 100 aún no existe
INSERT INTO miembros_demo VALUES (100, 'Ana', 1);
INSERT INTO miembros_demo VALUES (101, 'Luis', 1);
COMMIT; -- aquí se comprueban las FK
SELECT * FROM equipos_demo;
1 fila creada.
1 fila creada.
1 fila creada.
Confirmación terminada.
EQUIPO_ID NOMBRE CAPITAN_ID
---------- ------------------------------ ----------
1 Los Ágiles 100
1 fila seleccionada.
¿Y si en el COMMIT los datos siguen siendo incorrectos?
INSERT INTO equipos_demo VALUES (2, 'Los Fantasmas', 999); -- el capitán 999 no llegará nunca
COMMIT;
SELECT * FROM equipos_demo;
1 fila creada.
COMMIT
*
ERROR en línea 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CURSO.FK_EQU_CAPITAN) violated - parent key not found
EQUIPO_ID NOMBRE CAPITAN_ID
---------- ------------------------------ ----------
1 Los Ágiles 100
1 fila seleccionada.
ORA-02091: toda la transacción se ha deshecho, no solo la fila culpable.
12.3. SET CONSTRAINTS
Con DEFERRABLE INITIALLY IMMEDIATE, la restricción se comporta normalmente, pero una transacción concreta puede diferirla:
ALTER TABLE miembros_demo DROP CONSTRAINT fk_mie_equipo;
ALTER TABLE miembros_demo ADD CONSTRAINT fk_mie_equipo
FOREIGN KEY (equipo_id) REFERENCES equipos_demo (equipo_id)
DEFERRABLE INITIALLY IMMEDIATE;
INSERT INTO miembros_demo VALUES (102, 'Marta', 3); -- inmediata: falla
ERROR en línea 1:
ORA-02291: integrity constraint (CURSO.FK_MIE_EQUIPO) violated - parent key not found
SET CONSTRAINTS fk_mie_equipo DEFERRED; -- solo para esta transacción
INSERT INTO miembros_demo VALUES (102, 'Marta', 3); -- ahora se acepta
INSERT INTO equipos_demo VALUES (3, 'Los Nuevos', 102);
SET CONSTRAINTS ALL IMMEDIATE; -- comprobar ya, antes del COMMIT
COMMIT;
1 fila creada.
1 fila creada.
Confirmación terminada.
| Sentencia | Efecto |
|---|---|
SET CONSTRAINTS nombre1, nombre2 DEFERRED | Difiere esas restricciones hasta el final de la transacción actual |
SET CONSTRAINTS ALL DEFERRED | Difiere todas las restricciones diferibles |
SET CONSTRAINTS ALL IMMEDIATE | Las comprueba en ese momento (si fallan, da error sin deshacer la transacción, así que puedes corregir) |
ALTER SESSION SET CONSTRAINTS = DEFERRED \| IMMEDIATE \| DEFAULT | Lo mismo para toda la sesión |
Una restricción no diferible no se puede diferir:
SET CONSTRAINTS pk_equipos_demo DEFERRED;
ERROR en línea 1:
ORA-02447: cannot defer a constraint that is not deferrable
💡 Consejo:
SET CONSTRAINTS ALL IMMEDIATEjusto antes delCOMMITes una buena práctica: si algo falla, recibes el error y puedes corregirlo, en lugar de perder toda la transacción en elCOMMIT.
Para borrar tablas con referencias circulares, primero hay que eliminar una de las FK (o usar DROP TABLE ... CASCADE CONSTRAINTS):
ALTER TABLE equipos_demo DROP CONSTRAINT fk_equ_capitan;
DROP TABLE miembros_demo PURGE;
DROP TABLE equipos_demo PURGE;
13. Errores típicos de las restricciones
| Código | Mensaje (resumido) | Causa |
|---|---|---|
ORA-00001 | unique constraint (…) violated | Valor repetido en una PRIMARY KEY o UNIQUE |
ORA-01400 | cannot insert NULL into (…) | INSERT sin valor en una columna NOT NULL o de la PK |
ORA-01407 | cannot update (…) to NULL | UPDATE a nulo de una columna NOT NULL (o ON DELETE SET NULL sobre ella) |
ORA-02290 | check constraint (…) violated | La fila no cumple un CHECK |
ORA-02291 | integrity constraint (…) violated - parent key not found | FK con un valor que no existe en el padre |
ORA-02292 | integrity constraint (…) violated - child record found | Borrar o cambiar la clave de un padre con hijos |
ORA-02449 | unique/primary keys in table referenced by foreign keys | DROP TABLE de una tabla padre |
ORA-02270 | no matching unique or primary key for this column-list | FK hacia columnas que no son PK ni UNIQUE |
ORA-02260 | table can have only one primary key | Dos PRIMARY KEY |
ORA-02436 / ORA-02251 | date or system variable… / subquery not allowed | CHECK con SYSDATE/USER o con subconsulta |
ORA-02293 / ORA-02437 / ORA-02298 | cannot validate (…) | Activar un CHECK / PK / FK con datos que la incumplen |
ORA-02091 | transaction rolled back | Una restricción diferida falla en el COMMIT |
ORA-02447 | cannot defer a constraint that is not deferrable | SET CONSTRAINTS sobre una restricción no diferible |
Todos estos errores aparecen con su nombre de restricción entre paréntesis (CURSO.FK_EMP_DEPT): otro motivo para dar nombres claros.
14. Las restricciones en el diccionario de datos
14.1. USER_CONSTRAINTS
Una fila por restricción. Las columnas más útiles:
| Columna | Contenido |
|---|---|
CONSTRAINT_NAME | Nombre |
CONSTRAINT_TYPE | P (primary key), U (unique), R (referential: foreign key), C (check, incluido NOT NULL); en vistas aparecen además V (with check option) y O (read only) |
TABLE_NAME | Tabla a la que pertenece |
SEARCH_CONDITION_VC | Condición de los CHECK como texto (la columna SEARCH_CONDITION es de tipo LONG, incómodo de usar) |
R_CONSTRAINT_NAME | Para las FK, la PK o UNIQUE a la que apuntan |
DELETE_RULE | NO ACTION, CASCADE o SET NULL |
STATUS, VALIDATED | ENABLED/DISABLED, VALIDATED/NOT VALIDATED |
DEFERRABLE, DEFERRED | Si es diferible y su modo inicial |
INDEX_NAME | Para PK y UNIQUE, el índice que usan |
Todas las restricciones de EMPLEADOS:
COLUMN search_condition_vc FORMAT A30
SELECT constraint_name, constraint_type, search_condition_vc, status
FROM user_constraints
WHERE table_name = 'EMPLEADOS'
ORDER BY constraint_type, constraint_name;
CONSTRAINT_NAME C SEARCH_CONDITION_VC STATUS
---------------------- - ------------------------------ --------
CK_EMP_COMISION C comision >= 0 ENABLED
CK_EMP_SALARIO C salario > 0 ENABLED
NN_EMP_APELLIDOS C "APELLIDOS" IS NOT NULL ENABLED
NN_EMP_EMAIL C "EMAIL" IS NOT NULL ENABLED
NN_EMP_FECHA_ALTA C "FECHA_ALTA" IS NOT NULL ENABLED
NN_EMP_NOMBRE C "NOMBRE" IS NOT NULL ENABLED
NN_EMP_OFICIO C "OFICIO" IS NOT NULL ENABLED
NN_EMP_SALARIO C "SALARIO" IS NOT NULL ENABLED
PK_EMPLEADOS P (nulo) ENABLED
FK_EMP_DEPT R (nulo) ENABLED
FK_EMP_JEFE R (nulo) ENABLED
UQ_EMP_EMAIL U (nulo) ENABLED
12 filas seleccionadas.
Las claves ajenas del esquema con su tabla padre (uniendo USER_CONSTRAINTS consigo misma a través de R_CONSTRAINT_NAME):
SELECT hija.table_name AS tabla_hija,
hija.constraint_name,
padre.table_name AS tabla_padre,
hija.delete_rule
FROM user_constraints hija
JOIN user_constraints padre ON padre.constraint_name = hija.r_constraint_name
WHERE hija.constraint_type = 'R'
ORDER BY hija.table_name, hija.constraint_name;
TABLA_HIJA CONSTRAINT_NAME TABLA_PADRE DELETE_RU
-------------- ---------------------- -------------- ---------
ASIGNACIONES FK_ASI_EMP EMPLEADOS NO ACTION
ASIGNACIONES FK_ASI_PROYECTO PROYECTOS CASCADE
CATEGORIAS FK_CAT_PADRE CATEGORIAS NO ACTION
CLIENTES FK_CLI_COMERCIAL EMPLEADOS NO ACTION
EMPLEADOS FK_EMP_DEPT DEPARTAMENTOS NO ACTION
EMPLEADOS FK_EMP_JEFE EMPLEADOS NO ACTION
LINEAS_PEDIDO FK_LIN_PEDIDO PEDIDOS CASCADE
LINEAS_PEDIDO FK_LIN_PRODUCTO PRODUCTOS NO ACTION
PEDIDOS FK_PED_CLIENTE CLIENTES NO ACTION
PEDIDOS FK_PED_EMPLEADO EMPLEADOS NO ACTION
PRODUCTOS FK_PRO_CATEGORIA CATEGORIAS NO ACTION
PROYECTOS FK_PRY_DEPT DEPARTAMENTOS NO ACTION
12 filas seleccionadas.
(La sintaxis JOIN ... ON se explica en el módulo 30; de momento basta con entender qué devuelve.)
14.2. USER_CONS_COLUMNS
Indica qué columnas forman cada restricción y en qué posición:
SELECT c.constraint_name, c.constraint_type, cc.column_name, cc.position
FROM user_constraints c
JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE c.table_name = 'LINEAS_PEDIDO'
AND c.constraint_type IN ('P', 'R', 'U')
ORDER BY c.constraint_name, cc.position;
CONSTRAINT_NAME C COLUMN_NAME POSITION
---------------------- - ---------------- ----------
FK_LIN_PEDIDO R PEDIDO_ID 1
FK_LIN_PRODUCTO R PRODUCTO_ID 1
PK_LINEAS_PEDIDO P PEDIDO_ID 1
PK_LINEAS_PEDIDO P NUM_LINEA 2
4 filas seleccionadas.
pedido_id aparece dos veces: forma parte de la clave primaria y es clave ajena hacia PEDIDOS, la firma típica de una entidad débil.
💡 Consejo: En SQL Developer, al abrir una tabla tienes la pestaña Restricciones con toda esta información, y en el Data Modeler puedes generar el diagrama de un esquema existente (ingeniería inversa) a partir de sus claves ajenas.
15. Dominios SQL (Oracle 23ai)
⚠️ Importante: Disponible desde Oracle 23ai. En versiones anteriores, repite el tipo y el
CHECKen cada columna.
En el modelo relacional, un dominio es el conjunto de valores válidos de un atributo (módulo 16). El estándar SQL-92 los incluía con CREATE DOMAIN, y Oracle los incorporó en 23ai: un dominio agrupa un tipo de dato, un valor por defecto opcional y restricciones, con un nombre reutilizable en muchas columnas y tablas.
CREATE DOMAIN dom_email AS VARCHAR2(100)
CONSTRAINT ck_dom_email CHECK (REGEXP_LIKE(dom_email, '^[^@ ]+@[^@ ]+\.[a-z]{2,}$', 'i'));
CREATE DOMAIN dom_porcentaje AS NUMBER(5,2)
DEFAULT 0
CONSTRAINT ck_dom_porcentaje CHECK (dom_porcentaje BETWEEN 0 AND 100);
Dentro de la condición, el nombre del dominio representa el valor de la columna. Para usarlo, se pone en lugar del tipo (opcionalmente precedido de la palabra DOMAIN):
CREATE TABLE contactos_demo (
contacto_id NUMBER(6) CONSTRAINT pk_contactos_demo PRIMARY KEY,
email DOMAIN dom_email,
descuento dom_porcentaje
);
INSERT INTO contactos_demo (contacto_id, email) VALUES (1, 'ana@correo.es');
INSERT INTO contactos_demo (contacto_id, email) VALUES (2, 'ana.correo.es');
INSERT INTO contactos_demo VALUES (3, 'luis@correo.com', 150);
1 fila creada.
INSERT INTO contactos_demo (contacto_id, email) VALUES (2, 'ana.correo.es')
*
ERROR en línea 1:
ORA-11534: check constraint (CURSO.SYS_C009972) involving column EMAIL due to domain constraint CURSO.CK_DOM_EMAIL of domain CURSO.DOM_EMAIL violated
INSERT INTO contactos_demo VALUES (3, 'luis@correo.com', 150)
*
ERROR en línea 1:
ORA-11534: check constraint (CURSO.SYS_C009971) involving column DESCUENTO due to domain constraint CURSO.CK_DOM_PORCENTAJE of domain
CURSO.DOM_PORCENTAJE violated
SELECT * FROM contactos_demo;
DESCRIBE contactos_demo
CONTACTO_ID EMAIL DESCUENTO
----------- -------------------- ---------
1 ana@correo.es 0
1 fila seleccionada.
Nombre ¿Nulo? Tipo
----------------------------------------- -------- ----------------------------
CONTACTO_ID NOT NULL NUMBER(6)
EMAIL VARCHAR2(100) CURSO.DOM_EMAIL
DESCUENTO NUMBER(5,2) CURSO.DOM_PORCENTAJE
La columna descuento ha heredado el DEFAULT 0 del dominio. Al usar el dominio, Oracle crea en la tabla una restricción CHECK derivada (SYS_C...), y la columna DOMAIN_NAME de USER_TAB_COLUMNS indica el dominio de cada columna. Un dominio en uso no se puede borrar:
DROP DOMAIN dom_email;
ERROR en línea 1:
ORA-11502: The domain DOM_EMAIL to be dropped has dependent objects.
Existen también los dominios de enumeración, que definen una lista cerrada de valores con nombre (se guardan como números):
CREATE DOMAIN dom_estado_pedido AS ENUM (pendiente, enviado, entregado, cancelado);
CREATE TABLE envios_demo (envio_id NUMBER(6), estado dom_estado_pedido);
INSERT INTO envios_demo VALUES (1, dom_estado_pedido.enviado);
INSERT INTO envios_demo VALUES (2, 3);
INSERT INTO envios_demo VALUES (3, 7); -- no existe el valor 7
SELECT envio_id, estado, DOMAIN_DISPLAY(estado) AS texto FROM envios_demo;
ERROR en línea 1:
ORA-11534: check constraint (CURSO.SYS_C0010046) involving column ESTADO due to domain constraint CURSO.SYS_DOMAIN_C0045 of domain
CURSO.DOM_ESTADO_PEDIDO violated
ENVIO_ID ESTADO TEXTO
---------- ---------- ------------
1 2 ENVIADO
2 3 ENTREGADO
2 filas seleccionadas.
Los nombres de la enumeración son identificadores (sin comillas), no textos: con ('PENDIENTE', ...) la creación falla. La función DOMAIN_DISPLAY devuelve el nombre del valor.
💡 Consejo: Los dominios son la forma más limpia de aplicar la misma regla (formato de correo, de NIF, de código postal, porcentajes) en todo el esquema, y documentan el significado de las columnas. Pero si tu centro o tu empresa trabaja con 19c, usa el tipo y el
CHECKen cada columna, como en EMPRESA.
16. Del diseño E/R a las restricciones SQL
En el módulo 17 aprendiste a transformar un diagrama E/R en un esquema relacional. Ahora completamos el recorrido: cómo se implementa cada restricción del diseño en Oracle y cuáles no pueden declararse.
16.1. Tabla de correspondencias
| Elemento del diseño | Implementación en Oracle | ¿Declarativa? |
|---|---|---|
| Identificador principal | PRIMARY KEY | Sí |
| Identificador alternativo | UNIQUE + NOT NULL | Sí |
| Atributo obligatorio / opcional | NOT NULL / columna que admite nulos | Sí |
| Dominio de un atributo | Tipo de dato + CHECK (o DOMAIN, 23ai) | Sí |
| Atributo derivado de la misma fila | Columna virtual | Sí |
| Atributo derivado de otras tablas | Vista (módulo 26) | Sí, como consulta |
| Atributo multivaluado | Tabla aparte con FK y PK compuesta | Sí |
| Relación 1:N, participación opcional del hijo, por ejemplo (0,1) junto a DEPARTAMENTO | FK que admite nulos | Sí |
| Relación 1:N, participación obligatoria del hijo, (1,1) junto al padre | FK NOT NULL | Sí |
| Relación 1:1 | FK UNIQUE (y NOT NULL si es obligatoria) | Sí |
| Relación N:M | Tabla con PK compuesta por las dos FK | Sí |
| Entidad débil | PK = FK del propietario + clave parcial; ON DELETE CASCADE | Sí |
| Relación reflexiva | FK a la misma tabla + CHECK contra el ciclo trivial | Parcial (ciclos largos no) |
| Arco de exclusividad entre relaciones | Varias FK opcionales + CHECK de “exactamente una” | Sí |
| Jerarquía: subtipos | Tabla por subtipo con PK que es FK al supertipo | Sí |
| Jerarquía exclusiva | Discriminador + UNIQUE (id, tipo) + FK compuesta | Sí |
| Jerarquía total (todo supertipo tiene subtipo) | — | No (aserción 26ai / disparador) |
| Cardinalidad mínima 1 en el lado “muchos”: todo departamento tiene al menos un empleado; todo pedido tiene al menos una línea | — | No (aserción 26ai / disparador) |
| Cardinalidad máxima concreta: como mucho 3 préstamos por socio | — | No (disparador) |
| Reglas entre tablas: la fecha de asignación no puede ser anterior al inicio del proyecto | — | No en 19c; aserción en 26ai |
| Reglas con la fecha actual: la fecha de nacimiento no puede ser futura | — | No (disparador o aplicación) |
16.2. Relaciones 1:1 con UNIQUE en la FK
Una sede tiene exactamente un director y cada director dirige exactamente una sede. La FK va en una de las dos tablas y, para que no se convierta en 1:N, lleva UNIQUE:
CREATE TABLE sedes_demo (
sede_id NUMBER(4) CONSTRAINT pk_sedes_demo PRIMARY KEY,
ciudad VARCHAR2(40) CONSTRAINT nn_sed_ciudad NOT NULL
);
CREATE TABLE directores_demo (
director_id NUMBER(6) CONSTRAINT pk_directores_demo PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_dir_nombre NOT NULL,
sede_id NUMBER(4) CONSTRAINT nn_dir_sede NOT NULL
CONSTRAINT uq_dir_sede UNIQUE
CONSTRAINT fk_dir_sede REFERENCES sedes_demo (sede_id)
);
INSERT INTO sedes_demo VALUES (1, 'Valencia');
INSERT INTO directores_demo VALUES (1, 'Eva', 1);
INSERT INTO directores_demo VALUES (2, 'Raúl', 1); -- segundo director para la misma sede
INSERT INTO directores_demo VALUES (2, 'Raúl', 1)
*
ERROR en línea 1:
ORA-00001: unique constraint (CURSO.UQ_DIR_SEDE) violated on table CURSO.DIRECTORES_DEMO columns (SEDE_ID)
ORA-03301: (ORA-00001 details) row with column values (SEDE_ID:1) already exists
Fíjate en que una columna puede tener varias restricciones en línea seguidas. El NOT NULL hace que todo director tenga sede; que toda sede tenga director (el otro lado de la cardinalidad mínima) no es declarable sin aserciones o disparadores. Si la relación fuera opcional en ambos sentidos (0,1)-(0,1), la FK admitiría nulos: UNIQUE permite varios NULL.
16.3. Arco de exclusividad con CHECK
Un contrato lo firma o bien un particular o bien una empresa, nunca los dos ni ninguno:
CREATE TABLE particulares_demo (nif VARCHAR2(9) CONSTRAINT pk_particulares_demo PRIMARY KEY);
CREATE TABLE empresas_demo (cif VARCHAR2(9) CONSTRAINT pk_empresas_demo PRIMARY KEY);
CREATE TABLE contratos_demo (
contrato_id NUMBER(8) CONSTRAINT pk_contratos_demo PRIMARY KEY,
particular_nif VARCHAR2(9) CONSTRAINT fk_ctr_particular REFERENCES particulares_demo (nif),
empresa_cif VARCHAR2(9) CONSTRAINT fk_ctr_empresa REFERENCES empresas_demo (cif),
CONSTRAINT ck_ctr_titular CHECK (
(CASE WHEN particular_nif IS NOT NULL THEN 1 ELSE 0 END) +
(CASE WHEN empresa_cif IS NOT NULL THEN 1 ELSE 0 END) = 1)
);
INSERT INTO particulares_demo VALUES ('12345678Z');
INSERT INTO empresas_demo VALUES ('B12345678');
INSERT INTO contratos_demo VALUES (1, '12345678Z', NULL); -- particular
INSERT INTO contratos_demo VALUES (2, NULL, 'B12345678'); -- empresa
INSERT INTO contratos_demo VALUES (3, '12345678Z', 'B12345678'); -- los dos
INSERT INTO contratos_demo VALUES (4, NULL, NULL); -- ninguno
1 fila creada.
1 fila creada.
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_CTR_TITULAR) violated
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_CTR_TITULAR) violated
La técnica de sumar CASE se generaliza a cualquier número de FK: = 1 significa “exactamente una”; <= 1, “como mucho una”. Con dos columnas también puede escribirse con AND/OR: (a IS NOT NULL AND b IS NULL) OR (a IS NULL AND b IS NOT NULL).
16.4. Jerarquía exclusiva con discriminador
Una persona es profesor o alumno, pero no ambas cosas. Truco declarativo: el supertipo guarda el tipo (discriminador) y declara UNIQUE (persona_id, tipo); cada subtipo tiene una columna tipo fija por CHECK y una FK compuesta (persona_id, tipo):
CREATE TABLE personal_demo (
persona_id NUMBER(6) CONSTRAINT pk_personal_demo PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_per_nombre NOT NULL,
tipo CHAR(1) CONSTRAINT nn_per_tipo NOT NULL
CONSTRAINT ck_per_tipo CHECK (tipo IN ('P', 'A')),
CONSTRAINT uq_per_id_tipo UNIQUE (persona_id, tipo)
);
CREATE TABLE profesores_demo (
persona_id NUMBER(6) CONSTRAINT pk_profesores_demo PRIMARY KEY,
tipo CHAR(1) DEFAULT 'P' CONSTRAINT ck_prf_tipo CHECK (tipo = 'P'),
departamento VARCHAR2(30),
CONSTRAINT fk_prf_persona FOREIGN KEY (persona_id, tipo)
REFERENCES personal_demo (persona_id, tipo)
);
CREATE TABLE alumnos_demo (
persona_id NUMBER(6) CONSTRAINT pk_alumnos_demo PRIMARY KEY,
tipo CHAR(1) DEFAULT 'A' CONSTRAINT ck_alm_tipo CHECK (tipo = 'A'),
curso NUMBER(1),
CONSTRAINT fk_alm_persona FOREIGN KEY (persona_id, tipo)
REFERENCES personal_demo (persona_id, tipo)
);
INSERT INTO personal_demo VALUES (1, 'Profesora Ruiz', 'P');
INSERT INTO personal_demo VALUES (2, 'Alumno Pérez', 'A');
INSERT INTO profesores_demo (persona_id, departamento) VALUES (1, 'Informática');
INSERT INTO alumnos_demo (persona_id, curso) VALUES (2, 1);
INSERT INTO alumnos_demo (persona_id, curso) VALUES (1, 2); -- la profesora como alumna
INSERT INTO alumnos_demo (persona_id, tipo, curso) VALUES (1, 'P', 2); -- forzando el tipo
INSERT INTO alumnos_demo (persona_id, curso) VALUES (1, 2)
*
ERROR en línea 1:
ORA-02291: integrity constraint (CURSO.FK_ALM_PERSONA) violated - parent key not found
INSERT INTO alumnos_demo (persona_id, tipo, curso) VALUES (1, 'P', 2)
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_ALM_TIPO) violated
La persona 1 es de tipo 'P', así que el par (1, 'A') no existe en el supertipo y no puede ser alumna. Lo que no garantiza este diseño es la totalidad: puede haber una persona de tipo 'P' sin fila en profesores_demo.
16.5. Lo que no se puede declarar: aserciones y disparadores
Las restricciones que afectan a varias filas o varias tablas (cardinalidades mínimas en el lado “muchos”, máximos concretos, totalidad de jerarquías, comparaciones entre tablas) no se pueden expresar con CHECK. Durante décadas la única solución en Oracle ha sido programarlas con disparadores (triggers) en PL/SQL, que se estudian en el Curso de Oracle PL/SQL; en este curso no escribirás ninguno.
⚠️ Importante: Disponible desde Oracle AI Database 26ai (actualización 23.26.1). No existe en 19c, 21c ni en las primeras versiones de 23ai.
El estándar SQL-92 definió las aserciones (CREATE ASSERTION): restricciones de esquema con una condición que puede abarcar varias tablas. Oracle 26ai es el primer gran SGBD comercial que las implementa. El privilegio CREATE ASSERTION está incluido en DB_DEVELOPER_ROLE.
Ejemplo 1: “ningún empleado puede estar asignado a un proyecto antes de la fecha de inicio del proyecto” (una regla entre ASIGNACIONES y PROYECTOS):
CREATE ASSERTION as_asignacion_fecha CHECK (
NOT EXISTS (SELECT 1
FROM asignaciones a, proyectos p
WHERE a.proyecto_id = p.proyecto_id
AND a.fecha_asignacion < p.fecha_inicio)
);
UPDATE asignaciones
SET fecha_asignacion = DATE '2024-12-01'
WHERE emp_id = 120 AND proyecto_id = 1;
UPDATE asignaciones
*
ERROR en línea 1:
ORA-08601: Assertion (CURSO.AS_ASIGNACION_FECHA) violated.
DROP ASSERTION as_asignacion_fecha;
Ejemplo 2: “todo departamento tiene al menos un empleado” (cardinalidad mínima 1 en el lado “muchos”), con la sintaxis universal ALL ... SATISFY, sobre dos tablas de prueba. Debe ser diferible: si no, sería imposible insertar el primer departamento, que nace sin empleados.
CREATE TABLE dept_demo (
dept_id NUMBER(4) CONSTRAINT pk_dept_demo PRIMARY KEY,
nombre VARCHAR2(30) CONSTRAINT nn_ded_nombre NOT NULL
);
CREATE TABLE emp_demo (
emp_id NUMBER(6) CONSTRAINT pk_emp_demo PRIMARY KEY,
nombre VARCHAR2(30) CONSTRAINT nn_emd_nombre NOT NULL,
dept_id NUMBER(4) CONSTRAINT nn_emd_dept NOT NULL
CONSTRAINT fk_emd_dept REFERENCES dept_demo (dept_id)
);
CREATE ASSERTION as_dept_con_empleados CHECK (
ALL (SELECT d.dept_id FROM dept_demo d) d
SATISFY (EXISTS (SELECT 'x' FROM emp_demo e WHERE e.dept_id = d.dept_id))
) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO dept_demo VALUES (1, 'Ventas');
COMMIT; -- un departamento vacío
1 fila creada.
COMMIT
*
ERROR en línea 1:
ORA-02091: transaction rolled back
ORA-08601: Assertion (CURSO.AS_DEPT_CON_EMPLEADOS) violated.
INSERT INTO dept_demo VALUES (1, 'Ventas');
INSERT INTO emp_demo VALUES (10, 'Ana', 1);
COMMIT; -- ahora sí
DELETE FROM emp_demo WHERE emp_id = 10;
COMMIT; -- dejaría el departamento vacío
1 fila creada.
1 fila creada.
Confirmación terminada.
1 fila suprimida.
COMMIT
*
ERROR en línea 1:
ORA-02091: transaction rolled back
ORA-08601: Assertion (CURSO.AS_DEPT_CON_EMPLEADOS) violated.
Las aserciones de Oracle tienen limitaciones importantes, porque el SGBD debe poder comprobarlas de forma incremental y eficiente:
- No admiten funciones de agregado. Una regla como “como máximo 10 empleados por departamento” no se puede escribir como aserción:
CREATE ASSERTION as_max_empleados CHECK (
NOT EXISTS (SELECT dept_id FROM empleados GROUP BY dept_id HAVING COUNT(*) > 10)
);
ERROR en línea 2:
ORA-08689: CREATE ASSERTION failed
ORA-08661: Aggregates are not supported.
- No admiten la sintaxis
JOIN ... ON(hay que escribir las combinaciones en elWHERE, como en el ejemplo 1), y las subconsultas anidadas y las combinaciones deben cumplir ciertos requisitos para poder validarse de forma rápida (FAST). Por ejemplo, la versión de la regla del ejemplo 2 escrita con dosNOT EXISTSanidados sobre EMPRESA es rechazada conORA-08673(“does not meet the criteria to do a FAST validation”); la formaALL ... SATISFYes la recomendada para las reglas de “para todo… existe”. - Se gestionan con
ALTER ASSERTION ... ENABLE | DISABLE,DROP ASSERTIONy la vistaUSER_ASSERTIONS.
Para las reglas con agregados (máximos y mínimos concretos), las que dependen de la fecha actual y en versiones anteriores a 26ai, la solución sigue siendo un disparador.
💡 Consejo: Que una regla no se pueda declarar no significa que deba ignorarse. Documéntala en el diseño (en el diccionario de datos del proyecto o con
COMMENT ON, módulo 25) e indica dónde se controla: aserción, disparador, procedimiento de la aplicación o revisión periódica con una consulta.
17. Análisis de empresa_crear.sql
Ahora tienes todos los conocimientos para leer el script de creación de EMPRESA (empresa_crear.sql) como un profesional. Vamos tabla a tabla.
17.1. Orden de creación
Las tablas se crean en un orden en el que cada tabla padre existe antes que sus hijas:
flowchart LR
DEP[DEPARTAMENTOS] --> EMP[EMPLEADOS]
EMP --> EMP
EMP --> CLI[CLIENTES]
CAT[CATEGORIAS] --> CAT
CAT --> PRO[PRODUCTOS]
CLI --> PED[PEDIDOS]
EMP --> PED
PED --> LIN[LINEAS_PEDIDO]
PRO --> LIN
DEP --> PRY[PROYECTOS]
EMP --> ASI[ASIGNACIONES]
PRY --> ASI
(Cada flecha va del padre al hijo.) DEPARTAMENTOS → EMPLEADOS → CLIENTES → CATEGORIAS → PRODUCTOS → PEDIDOS → LINEAS_PEDIDO → PROYECTOS → ASIGNACIONES. Las autorreferencias (EMPLEADOS y CATEGORIAS) no plantean problema: la tabla ya existe cuando se evalúa su propia FK. empresa_borrar.sql borra en el orden inverso y además usa CASCADE CONSTRAINTS PURGE para no depender del orden y no dejar las tablas en la papelera de reciclaje (módulo 25).
17.2. DEPARTAMENTOS
CREATE TABLE departamentos (
dept_id NUMBER(4) CONSTRAINT pk_departamentos PRIMARY KEY, -- (1)
nombre VARCHAR2(40) CONSTRAINT nn_dep_nombre NOT NULL, -- (2)
localidad VARCHAR2(40), -- (3)
presupuesto NUMBER(12,2), -- (4)
CONSTRAINT uq_dep_nombre UNIQUE (nombre), -- (5)
CONSTRAINT ck_dep_presupuesto CHECK (presupuesto >= 0) -- (6)
);
- Clave primaria simple en línea: un entero de hasta 4 cifras (10, 20… hasta 9999 departamentos).
- El nombre es obligatorio…
- …la localidad, opcional.
- Presupuesto con céntimos y hasta 10 cifras enteras; opcional (el 70 no lo tiene).
- …y además el nombre es clave alternativa:
NOT NULL+UNIQUE(no puede haber dos departamentos llamados VENTAS). - Regla de dominio: el presupuesto no puede ser negativo. Si es nulo, el
CHECKlo acepta.
17.3. EMPLEADOS
CREATE TABLE empleados (
emp_id NUMBER(6) CONSTRAINT pk_empleados PRIMARY KEY,
nombre VARCHAR2(30) CONSTRAINT nn_emp_nombre NOT NULL,
apellidos VARCHAR2(50) CONSTRAINT nn_emp_apellidos NOT NULL,
email VARCHAR2(60) CONSTRAINT nn_emp_email NOT NULL, -- (1)
telefono VARCHAR2(15), -- (2)
fecha_alta DATE CONSTRAINT nn_emp_fecha_alta NOT NULL,
oficio VARCHAR2(30) CONSTRAINT nn_emp_oficio NOT NULL,
salario NUMBER(8,2) CONSTRAINT nn_emp_salario NOT NULL,
comision NUMBER(8,2), -- (3)
jefe_id NUMBER(6), -- (4)
dept_id NUMBER(4), -- (5)
CONSTRAINT uq_emp_email UNIQUE (email), -- (1)
CONSTRAINT ck_emp_salario CHECK (salario > 0), -- (6)
CONSTRAINT ck_emp_comision CHECK (comision >= 0), -- (3)
CONSTRAINT fk_emp_jefe FOREIGN KEY (jefe_id) REFERENCES empleados (emp_id), -- (4)
CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departamentos (dept_id) -- (5)
);
- El correo corporativo es otra clave alternativa (
NOT NULL+UNIQUE). - El teléfono es texto (puede tener prefijo) y opcional.
- La comisión es opcional (solo los vendedores) y, si existe, no negativa. Nulo y 0 son cosas distintas: Sergio tiene comisión 0 (es vendedor pero no ha cobrado), el resto de no vendedores no tiene comisión.
- Autorrelación 1:N “es jefe de”: FK a la propia tabla, opcional (la directora general no tiene jefe). Mismo tipo que
emp_id:NUMBER(6). - Relación 1:N con DEPARTAMENTOS, opcional (el becario 160 no tiene departamento). Acción por defecto
NO ACTION: no se puede cerrar un departamento con empleados. - Un salario debe ser positivo; con
NOT NULL, siempre existe.
17.4. CLIENTES
CREATE TABLE clientes (
cliente_id NUMBER(6) CONSTRAINT pk_clientes PRIMARY KEY,
nombre VARCHAR2(60) CONSTRAINT nn_cli_nombre NOT NULL,
nif VARCHAR2(9) CONSTRAINT nn_cli_nif NOT NULL, -- (1)
email VARCHAR2(60),
telefono VARCHAR2(15),
ciudad VARCHAR2(40),
provincia VARCHAR2(30),
fecha_alta DATE DEFAULT SYSDATE CONSTRAINT nn_cli_fecha_alta NOT NULL, -- (2)
limite_credito NUMBER(10,2) DEFAULT 3000, -- (3)
comercial_id NUMBER(6), -- (4)
CONSTRAINT uq_cli_nif UNIQUE (nif),
CONSTRAINT ck_cli_limite CHECK (limite_credito >= 0),
CONSTRAINT fk_cli_comercial FOREIGN KEY (comercial_id) REFERENCES empleados (emp_id)
);
- Clave natural (NIF) como alternativa a la sustituta
cliente_id.VARCHAR2(9)y noNUMBER, porque los NIF de empresas empiezan por letra. DEFAULT+NOT NULL: si no se indica la fecha de alta, se pone la actual; y nunca puede quedar vacía. El orden correcto es primeroDEFAULTy después la restricción.- Valor por defecto sin
NOT NULL: si elINSERTno lo menciona vale 3000, pero alguien podría insertarNULLexplícitamente (“sin límite definido”). - FK opcional hacia EMPLEADOS con papel de comercial: la FK no tiene por qué llamarse igual que la columna referenciada.
17.5. CATEGORIAS y PRODUCTOS
CREATE TABLE categorias (
categoria_id NUMBER(4) CONSTRAINT pk_categorias PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_cat_nombre NOT NULL,
categoria_padre_id NUMBER(4), -- (1)
CONSTRAINT uq_cat_nombre UNIQUE (nombre),
CONSTRAINT fk_cat_padre FOREIGN KEY (categoria_padre_id) REFERENCES categorias (categoria_id)
);
CREATE TABLE productos (
producto_id NUMBER(6) CONSTRAINT pk_productos PRIMARY KEY,
nombre VARCHAR2(60) CONSTRAINT nn_pro_nombre NOT NULL, -- (2)
descripcion VARCHAR2(200),
categoria_id NUMBER(4) CONSTRAINT nn_pro_categoria NOT NULL, -- (3)
precio NUMBER(8,2) CONSTRAINT nn_pro_precio NOT NULL,
stock NUMBER(6) DEFAULT 0 CONSTRAINT nn_pro_stock NOT NULL,
fecha_alta DATE,
descatalogado CHAR(1) DEFAULT 'N' CONSTRAINT nn_pro_descatalogado NOT NULL, -- (4)
CONSTRAINT ck_pro_precio CHECK (precio >= 0),
CONSTRAINT ck_pro_stock CHECK (stock >= 0),
CONSTRAINT ck_pro_descatalogado CHECK (descatalogado IN ('S', 'N')), -- (4)
CONSTRAINT fk_pro_categoria FOREIGN KEY (categoria_id) REFERENCES categorias (categoria_id)
);
- Jerarquía de categorías: autorreferencia opcional (las raíces no tienen padre).
- El nombre de producto no es
UNIQUE(decisión de diseño: podría haber dos productos con el mismo nombre comercial). - FK obligatoria: todo producto pertenece a una categoría, que en el E/R es la cardinalidad (1,1) junto a CATEGORIA.
- El “booleano clásico”:
CHAR(1)+DEFAULT 'N'+NOT NULL+CHECK IN ('S','N'). En 23ai podría serBOOLEAN DEFAULT FALSE NOT NULL; el script usa la versión compatible con 19c.
17.6. PEDIDOS y LINEAS_PEDIDO
CREATE TABLE pedidos (
pedido_id NUMBER(8) CONSTRAINT pk_pedidos PRIMARY KEY,
cliente_id NUMBER(6) CONSTRAINT nn_ped_cliente NOT NULL, -- (1)
empleado_id NUMBER(6), -- (2)
fecha_pedido DATE DEFAULT SYSDATE CONSTRAINT nn_ped_fecha NOT NULL,
fecha_envio DATE,
estado VARCHAR2(12) DEFAULT 'PENDIENTE' CONSTRAINT nn_ped_estado NOT NULL,
CONSTRAINT ck_ped_estado CHECK (estado IN ('PENDIENTE', 'ENVIADO', 'ENTREGADO', 'CANCELADO')), -- (3)
CONSTRAINT ck_ped_fechas CHECK (fecha_envio >= fecha_pedido), -- (4)
CONSTRAINT fk_ped_cliente FOREIGN KEY (cliente_id) REFERENCES clientes (cliente_id),
CONSTRAINT fk_ped_empleado FOREIGN KEY (empleado_id) REFERENCES empleados (emp_id)
);
CREATE TABLE lineas_pedido (
pedido_id NUMBER(8), -- (5)
num_linea NUMBER(3),
producto_id NUMBER(6) CONSTRAINT nn_lin_producto NOT NULL,
cantidad NUMBER(5) CONSTRAINT nn_lin_cantidad NOT NULL,
precio_unitario NUMBER(8,2) CONSTRAINT nn_lin_precio NOT NULL, -- (6)
descuento NUMBER(4,2) DEFAULT 0 CONSTRAINT nn_lin_descuento NOT NULL,
CONSTRAINT pk_lineas_pedido PRIMARY KEY (pedido_id, num_linea), -- (5)
CONSTRAINT ck_lin_cantidad CHECK (cantidad > 0),
CONSTRAINT ck_lin_precio CHECK (precio_unitario >= 0),
CONSTRAINT ck_lin_descuento CHECK (descuento BETWEEN 0 AND 100),
CONSTRAINT fk_lin_pedido FOREIGN KEY (pedido_id) REFERENCES pedidos (pedido_id) ON DELETE CASCADE, -- (7)
CONSTRAINT fk_lin_producto FOREIGN KEY (producto_id) REFERENCES productos (producto_id) -- (8)
);
- Todo pedido es de un cliente (FK obligatoria)…
- …pero no todos los gestiona un empleado (pedidos en línea): FK opcional.
- Dominio cerrado de estados con
CHECK ... IN. Alternativas: una tablaestadoscon FK (si la lista cambia a menudo o tiene más atributos) o un dominio de enumeración (23ai). - Regla entre dos columnas de la misma fila. Con
fecha_envionula (pendiente), elCHECKes desconocido y se acepta. - Entidad débil: la clave de la línea es el pedido más un número de línea (clave parcial).
pedido_ides a la vez parte de la PK y FK; ninguna de las dos columnas necesitaNOT NULLexplícito. - El precio se copia en la línea en el momento de la venta (redundancia controlada): si mañana cambia el precio del producto, los pedidos antiguos conservan el precio al que se vendieron.
ON DELETE CASCADE: las líneas no tienen sentido sin su pedido.NO ACTIONhacia PRODUCTOS: no se puede borrar un producto que se ha vendido (por eso el 14 está descatalogado en lugar de borrado: es un borrado lógico).
17.7. PROYECTOS y ASIGNACIONES
CREATE TABLE proyectos (
proyecto_id NUMBER(4) CONSTRAINT pk_proyectos PRIMARY KEY,
nombre VARCHAR2(60) CONSTRAINT nn_pry_nombre NOT NULL,
dept_id NUMBER(4),
fecha_inicio DATE CONSTRAINT nn_pry_inicio NOT NULL,
fecha_fin DATE,
presupuesto NUMBER(12,2),
CONSTRAINT uq_pry_nombre UNIQUE (nombre),
CONSTRAINT ck_pry_fechas CHECK (fecha_fin >= fecha_inicio),
CONSTRAINT ck_pry_presupuesto CHECK (presupuesto >= 0),
CONSTRAINT fk_pry_dept FOREIGN KEY (dept_id) REFERENCES departamentos (dept_id)
);
CREATE TABLE asignaciones (
emp_id NUMBER(6),
proyecto_id NUMBER(4),
fecha_asignacion DATE DEFAULT SYSDATE CONSTRAINT nn_asi_fecha NOT NULL,
horas NUMBER(5) DEFAULT 0,
funcion VARCHAR2(30),
CONSTRAINT pk_asignaciones PRIMARY KEY (emp_id, proyecto_id), -- (1)
CONSTRAINT ck_asi_horas CHECK (horas >= 0),
CONSTRAINT fk_asi_emp FOREIGN KEY (emp_id) REFERENCES empleados (emp_id), -- (2)
CONSTRAINT fk_asi_proyecto FOREIGN KEY (proyecto_id) REFERENCES proyectos (proyecto_id) ON DELETE CASCADE -- (3)
);
- Relación N:M entre EMPLEADOS y PROYECTOS convertida en tabla: la PK es la combinación de las dos FK (un empleado no puede estar asignado dos veces al mismo proyecto).
fecha_asignacion,horasyfuncionson los atributos de la relación. - Borrar un empleado con asignaciones se impide (
NO ACTION): conserva el historial de quién trabajó en qué. - Borrar un proyecto borra sus asignaciones (
CASCADE): sin el proyecto, la asignación no significa nada.
Reglas del negocio que el script no puede garantizar de forma declarativa (y que en 26ai podrían cubrirse en parte con aserciones): que la fecha de asignación sea posterior al inicio del proyecto, que todo pedido tenga al menos una línea, que la jerarquía de jefes no tenga ciclos, o que el comercial de un cliente sea realmente un VENDEDOR.
18. Diseño de bases de datos completas
Para terminar, dos casos completos de principio a fin: requisitos, modelo, esquema relacional y script DDL probado.
18.1. Caso 1: biblioteca
Requisitos. Una biblioteca municipal quiere gestionar su fondo y sus préstamos:
- De cada libro se guarda el ISBN (10 o 13 caracteres), el título, la editorial (obligatoria), el año de publicación, el idioma (español por defecto) y el número de páginas.
- Un libro puede tener varios autores, en un orden de firma; un autor puede haber escrito varios libros. De cada autor: nombre, nacionalidad y año de nacimiento.
- De cada libro hay uno o varios ejemplares físicos, numerados dentro de cada libro (ejemplar 1, 2…), con su estado (disponible, prestado, en reparación, de baja) y su fecha de adquisición. Si se elimina un libro del catálogo, desaparecen sus ejemplares.
- Los socios tienen DNI (o NIE), nombre, apellidos, tipo (estudiante, profesor o externo), fecha de alta y al menos un dato de contacto (correo o teléfono).
- Un préstamo relaciona un socio con un ejemplar en una fecha, con una fecha de devolución prevista posterior y la fecha de devolución real cuando se produce.
Modelo E/R (resumido, sin todos los atributos):
flowchart LR
classDef debil stroke-width:4px
EDITORIAL[EDITORIAL] ---|"(1,1)"| publica{publica}
publica ---|"(0,n)"| LIBRO[LIBRO]
AUTOR[AUTOR] ---|"(1,n)"| escribe{escribe}
escribe ---|"(0,n)"| LIBRO
escribe --- e_orden([orden])
LIBRO ---|"(1,1)"| tiene{tiene}:::debil
tiene ---|"(0,n)"| EJEMPLAR[EJEMPLAR]:::debil
EJEMPLAR --- ej_num([<u>num_ejemplar</u> parcial])
SOCIO[SOCIO] ---|"(1,1)"| realiza{realiza}
realiza ---|"(0,n)"| PRESTAMO[PRESTAMO]
EJEMPLAR ---|"(1,1)"| sobre{sobre}
sobre ---|"(0,n)"| PRESTAMO
Esquema relacional:
EDITORIALES (editorial_id, nombre, pais (N)) · UK: nombre
AUTORES (autor_id, nombre, nacionalidad (N), anio_nacimiento (N))
LIBROS (isbn, titulo, editorial_id, anio_publicacion (N), idioma, paginas (N))
- editorial_id → EDITORIALES(editorial_id)
LIBROS_AUTORES (*isbn*, *autor_id*, orden) · UK: (isbn, orden)
- isbn → LIBROS(isbn) con borrado en cascada · autor_id → AUTORES(autor_id)
EJEMPLARES (*isbn*, num_ejemplar, estado, fecha_adquisicion)
- isbn → LIBROS(isbn) con borrado en cascada
SOCIOS (socio_id, dni, nombre, apellidos, email (N), telefono (N), tipo, fecha_alta) · UK: dni · UK: email
PRESTAMOS (prestamo_id, isbn, num_ejemplar, socio_id, fecha_prestamo, fecha_prevista, fecha_devolucion (N))
- (isbn, num_ejemplar) → EJEMPLARES(isbn, num_ejemplar) · socio_id → SOCIOS(socio_id)
Script DDL:
-- =====================================================================
-- BIBLIOTECA - Script de creación
-- =====================================================================
CREATE TABLE editoriales (
editorial_id NUMBER(4) CONSTRAINT pk_editoriales PRIMARY KEY,
nombre VARCHAR2(60) CONSTRAINT nn_edi_nombre NOT NULL,
pais VARCHAR2(40),
CONSTRAINT uq_edi_nombre UNIQUE (nombre)
);
CREATE TABLE autores (
autor_id NUMBER(6) CONSTRAINT pk_autores PRIMARY KEY,
nombre VARCHAR2(80) CONSTRAINT nn_aut_nombre NOT NULL,
nacionalidad VARCHAR2(40),
anio_nacimiento NUMBER(4),
CONSTRAINT ck_aut_anio CHECK (anio_nacimiento BETWEEN 1000 AND 2100)
);
CREATE TABLE libros (
isbn VARCHAR2(13) CONSTRAINT pk_libros PRIMARY KEY,
titulo VARCHAR2(150) CONSTRAINT nn_lib_titulo NOT NULL,
editorial_id NUMBER(4) CONSTRAINT nn_lib_editorial NOT NULL,
anio_publicacion NUMBER(4),
idioma CHAR(2) DEFAULT 'ES' CONSTRAINT nn_lib_idioma NOT NULL,
paginas NUMBER(5),
CONSTRAINT ck_lib_isbn CHECK (REGEXP_LIKE(isbn, '^([0-9]{9}[0-9X]|[0-9]{13})$')),
CONSTRAINT ck_lib_anio CHECK (anio_publicacion BETWEEN 1450 AND 2100),
CONSTRAINT ck_lib_paginas CHECK (paginas > 0),
CONSTRAINT fk_lib_editorial FOREIGN KEY (editorial_id) REFERENCES editoriales (editorial_id)
);
-- Relación N:M entre LIBROS y AUTORES con el orden de firma
CREATE TABLE libros_autores (
isbn VARCHAR2(13),
autor_id NUMBER(6),
orden NUMBER(2) DEFAULT 1 CONSTRAINT nn_lia_orden NOT NULL,
CONSTRAINT pk_libros_autores PRIMARY KEY (isbn, autor_id),
CONSTRAINT uq_lia_orden UNIQUE (isbn, orden),
CONSTRAINT ck_lia_orden CHECK (orden >= 1),
CONSTRAINT fk_lia_libro FOREIGN KEY (isbn) REFERENCES libros (isbn) ON DELETE CASCADE,
CONSTRAINT fk_lia_autor FOREIGN KEY (autor_id) REFERENCES autores (autor_id)
);
-- Entidad débil: cada ejemplar se identifica por el libro y un número
CREATE TABLE ejemplares (
isbn VARCHAR2(13),
num_ejemplar NUMBER(3),
estado VARCHAR2(10) DEFAULT 'DISPONIBLE' CONSTRAINT nn_eje_estado NOT NULL,
fecha_adquisicion DATE DEFAULT SYSDATE CONSTRAINT nn_eje_fecha NOT NULL,
CONSTRAINT pk_ejemplares PRIMARY KEY (isbn, num_ejemplar),
CONSTRAINT ck_eje_num CHECK (num_ejemplar >= 1),
CONSTRAINT ck_eje_estado CHECK (estado IN ('DISPONIBLE', 'PRESTADO', 'REPARACION', 'BAJA')),
CONSTRAINT fk_eje_libro FOREIGN KEY (isbn) REFERENCES libros (isbn) ON DELETE CASCADE
);
CREATE TABLE socios (
socio_id NUMBER(6) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1)
CONSTRAINT pk_socios PRIMARY KEY,
dni VARCHAR2(9) CONSTRAINT nn_soc_dni NOT NULL,
nombre VARCHAR2(40) CONSTRAINT nn_soc_nombre NOT NULL,
apellidos VARCHAR2(60) CONSTRAINT nn_soc_apellidos NOT NULL,
email VARCHAR2(80),
telefono VARCHAR2(15),
tipo VARCHAR2(10) DEFAULT 'EXTERNO' CONSTRAINT nn_soc_tipo NOT NULL,
fecha_alta DATE DEFAULT SYSDATE CONSTRAINT nn_soc_fecha NOT NULL,
CONSTRAINT uq_soc_dni UNIQUE (dni),
CONSTRAINT uq_soc_email UNIQUE (email),
CONSTRAINT ck_soc_dni CHECK (REGEXP_LIKE(dni, '^[0-9XYZ][0-9]{7}[A-Z]$')),
CONSTRAINT ck_soc_tipo CHECK (tipo IN ('ESTUDIANTE', 'PROFESOR', 'EXTERNO')),
CONSTRAINT ck_soc_contacto CHECK (email IS NOT NULL OR telefono IS NOT NULL)
);
CREATE TABLE prestamos (
prestamo_id NUMBER(8) GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_prestamos PRIMARY KEY,
isbn VARCHAR2(13) CONSTRAINT nn_pre_isbn NOT NULL,
num_ejemplar NUMBER(3) CONSTRAINT nn_pre_ejemplar NOT NULL,
socio_id NUMBER(6) CONSTRAINT nn_pre_socio NOT NULL,
fecha_prestamo DATE DEFAULT SYSDATE CONSTRAINT nn_pre_fecha NOT NULL,
fecha_prevista DATE CONSTRAINT nn_pre_prevista NOT NULL,
fecha_devolucion DATE,
CONSTRAINT ck_pre_prevista CHECK (fecha_prevista > fecha_prestamo),
CONSTRAINT ck_pre_devolucion CHECK (fecha_devolucion >= fecha_prestamo),
CONSTRAINT fk_pre_ejemplar FOREIGN KEY (isbn, num_ejemplar) REFERENCES ejemplares (isbn, num_ejemplar),
CONSTRAINT fk_pre_socio FOREIGN KEY (socio_id) REFERENCES socios (socio_id)
);
Decisiones de diseño destacables:
- La clave de LIBROS es natural (el ISBN), validada con una expresión regular; las de SOCIOS y PRESTAMOS son identidades. En SOCIOS se usa
BY DEFAULT ON NULLpara permitir migrar socios antiguos con su número, y en PRESTAMOSALWAYS. ck_soc_contactoexige al menos uno de dos atributos opcionales.- La FK de PRESTAMOS hacia EJEMPLARES es compuesta y ambas columnas son
NOT NULL(así no hay el problema de las FK compuestas parcialmente nulas). - El
ON DELETE CASCADEde EJEMPLARES hacia LIBROS se detiene en PRESTAMOS, cuya FK esNO ACTION: no se puede borrar un libro con préstamos registrados, aunque sí uno que nunca se ha prestado.
Prueba del script con algunos datos (incluidos casos que deben fallar):
INSERT INTO editoriales VALUES (1, 'Alfaguara', 'España');
INSERT INTO editoriales VALUES (2, 'Anagrama', 'España');
INSERT INTO autores VALUES (1, 'Isabel Allende', 'Chilena', 1942);
INSERT INTO autores VALUES (2, 'Rosa Montero', 'Española', 1951);
INSERT INTO libros VALUES ('9788401352836', 'La casa de los espíritus', 1, 1982, 'ES', 499);
INSERT INTO libros (isbn, titulo, editorial_id, anio_publicacion)
VALUES ('9788433978899', 'La ridícula idea de no volver a verte', 2, 2013);
INSERT INTO libros_autores (isbn, autor_id) VALUES ('9788401352836', 1);
INSERT INTO libros_autores (isbn, autor_id) VALUES ('9788433978899', 2);
INSERT INTO ejemplares (isbn, num_ejemplar) VALUES ('9788401352836', 1);
INSERT INTO ejemplares (isbn, num_ejemplar) VALUES ('9788401352836', 2);
INSERT INTO ejemplares (isbn, num_ejemplar) VALUES ('9788433978899', 1);
INSERT INTO socios (dni, nombre, apellidos, email, tipo)
VALUES ('12345678Z', 'Lucía', 'Pérez Gil', 'lucia@correo.es', 'ESTUDIANTE');
INSERT INTO socios (dni, nombre, apellidos, telefono) VALUES ('87654321X', 'Mario', 'Sanz Rey', '600111222');
INSERT INTO prestamos (isbn, num_ejemplar, socio_id, fecha_prestamo, fecha_prevista)
VALUES ('9788401352836', 1, 1, DATE '2026-09-20', DATE '2026-10-04');
COMMIT;
-- Casos que deben fallar
INSERT INTO libros (isbn, titulo, editorial_id) VALUES ('978-84-01', 'ISBN con guiones', 1);
INSERT INTO socios (dni, nombre, apellidos) VALUES ('11111111H', 'Sin', 'Contacto');
INSERT INTO prestamos (isbn, num_ejemplar, socio_id, fecha_prevista)
VALUES ('9788433978899', 5, 1, SYSDATE + 15);
INSERT INTO prestamos (isbn, num_ejemplar, socio_id, fecha_prestamo, fecha_prevista)
VALUES ('9788433978899', 1, 2, DATE '2026-10-01', DATE '2026-09-01');
DELETE FROM libros WHERE isbn = '9788401352836';
ORA-02290: check constraint (CURSO.CK_LIB_ISBN) violated
ORA-02290: check constraint (CURSO.CK_SOC_CONTACTO) violated
ORA-02291: integrity constraint (CURSO.FK_PRE_EJEMPLAR) violated - parent key not found
ORA-02290: check constraint (CURSO.CK_PRE_PREVISTA) violated
ORA-02292: integrity constraint (CURSO.FK_PRE_EJEMPLAR) violated - child record found
En cambio, el libro sin préstamos sí se puede borrar, y se lleva consigo su ejemplar y su autoría:
DELETE FROM libros WHERE isbn = '9788433978899';
SELECT COUNT(*) AS ejemplares FROM ejemplares;
SELECT COUNT(*) AS autorias FROM libros_autores;
ROLLBACK;
1 fila suprimida.
EJEMPLARES
----------
2
1 fila seleccionada.
AUTORIAS
----------
1
1 fila seleccionada.
Rollback terminado.
Y los socios han recibido identificador, tipo y fecha automáticamente:
SELECT socio_id, dni, nombre, tipo, fecha_alta FROM socios;
SOCIO_ID DNI NOMBRE TIPO FECHA_ALTA
-------- --------- -------- ---------- ----------
1 12345678Z Lucía ESTUDIANTE 03/10/2026
2 87654321X Mario EXTERNO 03/10/2026
2 filas seleccionadas.
Restricciones no declarativas de la biblioteca (a controlar con disparadores o en la aplicación): que un ejemplar no tenga dos préstamos abiertos a la vez y que su estado coincida con la existencia de un préstamo abierto, un máximo de préstamos simultáneos por tipo de socio (requiere contar), que todo libro tenga al menos un autor y un ejemplar, y que la fecha de alta no sea futura.
Script de borrado, en orden inverso:
DROP TABLE prestamos PURGE;
DROP TABLE socios PURGE;
DROP TABLE ejemplares PURGE;
DROP TABLE libros_autores PURGE;
DROP TABLE libros PURGE;
DROP TABLE autores PURGE;
DROP TABLE editoriales PURGE;
18.2. Caso 2: academia de formación
Requisitos. Una academia imparte cursos en distintas ediciones:
- Cada curso tiene un nombre único, un nivel (básico, medio o avanzado; básico por defecto), una duración en horas y un precio. Cada curso puede tener un profesor coordinador, y cada profesor coordina como mucho un curso.
- Los profesores tienen NIF y correo únicos, nombre y tarifa por hora.
- Cada curso se imparte en varias ediciones, con fechas de inicio y fin, profesor (puede asignarse más tarde), modalidad (presencial por defecto u online), aula (obligatoria si es presencial) y plazas (entre 1 y 30, 20 por defecto). No puede haber dos ediciones del mismo curso que empiecen el mismo día.
- Los alumnos se matriculan en ediciones; de cada matrícula se guarda la fecha, la nota final (de 0 a 10) y quién paga: el propio alumno o una empresa (formación bonificada), pero no ambos.
- Si se cancela una edición, se eliminan sus matrículas. Si un profesor coordinador deja la academia, el curso se queda sin coordinador.
Esquema relacional:
PROFESORES (profesor_id, nif, nombre, email, tarifa_hora) · UK: nif · UK: email
CURSOS (curso_id, nombre, nivel, horas, precio, coordinador_id (N)) · UK: nombre · UK: coordinador_id
- coordinador_id → PROFESORES(profesor_id), al borrar se pone a nulo
EDICIONES (edicion_id, curso_id, profesor_id (N), fecha_inicio, fecha_fin, modalidad, aula (N), plazas) · UK: (curso_id, fecha_inicio)
- curso_id → CURSOS(curso_id) · profesor_id → PROFESORES(profesor_id)
EMPRESAS (empresa_id, cif, razon_social) · UK: cif
ALUMNOS (alumno_id, nif, nombre, fecha_nacimiento (N), email (N)) · UK: nif · UK: email
MATRICULAS (*alumno_id*, *edicion_id*, fecha_matricula, nota (N), paga_alumno, empresa_id (N))
- alumno_id → ALUMNOS(alumno_id) · edicion_id → EDICIONES(edicion_id) con borrado en cascada · empresa_id → EMPRESAS(empresa_id)
Script DDL:
-- =====================================================================
-- ACADEMIA - Script de creación
-- =====================================================================
CREATE TABLE profesores (
profesor_id NUMBER(4) CONSTRAINT pk_profesores PRIMARY KEY,
nif VARCHAR2(9) CONSTRAINT nn_prf_nif NOT NULL,
nombre VARCHAR2(60) CONSTRAINT nn_prf_nombre NOT NULL,
email VARCHAR2(80) CONSTRAINT nn_prf_email NOT NULL,
tarifa_hora NUMBER(6,2) CONSTRAINT nn_prf_tarifa NOT NULL,
CONSTRAINT uq_prf_nif UNIQUE (nif),
CONSTRAINT uq_prf_email UNIQUE (email),
CONSTRAINT ck_prf_tarifa CHECK (tarifa_hora > 0)
);
CREATE TABLE cursos (
curso_id NUMBER(4) CONSTRAINT pk_cursos PRIMARY KEY,
nombre VARCHAR2(80) CONSTRAINT nn_cur_nombre NOT NULL,
nivel VARCHAR2(8) DEFAULT 'BASICO' CONSTRAINT nn_cur_nivel NOT NULL,
horas NUMBER(4) CONSTRAINT nn_cur_horas NOT NULL,
precio NUMBER(7,2) CONSTRAINT nn_cur_precio NOT NULL,
coordinador_id NUMBER(4),
CONSTRAINT uq_cur_nombre UNIQUE (nombre),
CONSTRAINT uq_cur_coordinador UNIQUE (coordinador_id), -- relación 1:1
CONSTRAINT ck_cur_nivel CHECK (nivel IN ('BASICO', 'MEDIO', 'AVANZADO')),
CONSTRAINT ck_cur_horas CHECK (horas > 0),
CONSTRAINT ck_cur_precio CHECK (precio >= 0),
CONSTRAINT fk_cur_coordinador FOREIGN KEY (coordinador_id) REFERENCES profesores (profesor_id)
ON DELETE SET NULL
);
CREATE TABLE ediciones (
edicion_id NUMBER(6) CONSTRAINT pk_ediciones PRIMARY KEY,
curso_id NUMBER(4) CONSTRAINT nn_edi_curso NOT NULL,
profesor_id NUMBER(4),
fecha_inicio DATE CONSTRAINT nn_edi_inicio NOT NULL,
fecha_fin DATE CONSTRAINT nn_edi_fin NOT NULL,
modalidad VARCHAR2(10) DEFAULT 'PRESENCIAL' CONSTRAINT nn_edi_modalidad NOT NULL,
aula VARCHAR2(10),
plazas NUMBER(2) DEFAULT 20 CONSTRAINT nn_edi_plazas NOT NULL,
CONSTRAINT uq_edi_curso_inicio UNIQUE (curso_id, fecha_inicio),
CONSTRAINT ck_edi_fechas CHECK (fecha_fin >= fecha_inicio),
CONSTRAINT ck_edi_plazas CHECK (plazas BETWEEN 1 AND 30),
CONSTRAINT ck_edi_modalidad CHECK (modalidad IN ('PRESENCIAL', 'ONLINE')),
CONSTRAINT ck_edi_aula CHECK (modalidad = 'ONLINE' OR aula IS NOT NULL), -- obligatoria condicional
CONSTRAINT fk_edi_curso FOREIGN KEY (curso_id) REFERENCES cursos (curso_id),
CONSTRAINT fk_edi_profesor FOREIGN KEY (profesor_id) REFERENCES profesores (profesor_id)
);
CREATE TABLE empresas (
empresa_id NUMBER(6) CONSTRAINT pk_empresas PRIMARY KEY,
cif VARCHAR2(9) CONSTRAINT nn_emp_cif NOT NULL,
razon_social VARCHAR2(80) CONSTRAINT nn_emp_razon NOT NULL,
CONSTRAINT uq_emp_cif UNIQUE (cif)
);
CREATE TABLE alumnos (
alumno_id NUMBER(6) CONSTRAINT pk_alumnos PRIMARY KEY,
nif VARCHAR2(9) CONSTRAINT nn_alu_nif NOT NULL,
nombre VARCHAR2(60) CONSTRAINT nn_alu_nombre NOT NULL,
fecha_nacimiento DATE,
email VARCHAR2(80),
CONSTRAINT uq_alu_nif UNIQUE (nif),
CONSTRAINT uq_alu_email UNIQUE (email)
);
-- N:M entre ALUMNOS y EDICIONES con atributos; quién paga es un arco exclusivo
CREATE TABLE matriculas (
alumno_id NUMBER(6),
edicion_id NUMBER(6),
fecha_matricula DATE DEFAULT SYSDATE CONSTRAINT nn_mat_fecha NOT NULL,
nota NUMBER(4,2),
paga_alumno CHAR(1) DEFAULT 'S' CONSTRAINT nn_mat_paga NOT NULL,
empresa_id NUMBER(6),
CONSTRAINT pk_matriculas PRIMARY KEY (alumno_id, edicion_id),
CONSTRAINT ck_mat_nota CHECK (nota BETWEEN 0 AND 10),
CONSTRAINT ck_mat_paga CHECK (paga_alumno IN ('S', 'N')),
CONSTRAINT ck_mat_pagador CHECK ((paga_alumno = 'S' AND empresa_id IS NULL)
OR (paga_alumno = 'N' AND empresa_id IS NOT NULL)),
CONSTRAINT fk_mat_alumno FOREIGN KEY (alumno_id) REFERENCES alumnos (alumno_id),
CONSTRAINT fk_mat_edicion FOREIGN KEY (edicion_id) REFERENCES ediciones (edicion_id) ON DELETE CASCADE,
CONSTRAINT fk_mat_empresa FOREIGN KEY (empresa_id) REFERENCES empresas (empresa_id)
);
Aspectos destacables:
- Relación 1:1 opcional entre CURSOS y PROFESORES (coordinación) con una FK
UNIQUEque admite nulos, yON DELETE SET NULLpara el requisito “el curso se queda sin coordinador”. - Obligatoriedad condicional (
ck_edi_aula): el aula es obligatoria solo si la edición es presencial. Es un patrón muy útil:CHECK (condición_que_exime OR columna IS NOT NULL). - Clave alternativa compuesta
uq_edi_curso_inicio. - Arco exclusivo en MATRICULAS (
ck_mat_pagador) usando un indicador y la FK opcional.
Prueba:
INSERT INTO profesores VALUES (1, '11111111H', 'Elena Ruiz', 'elena@academia.es', 30);
INSERT INTO profesores VALUES (2, '22222222J', 'Jorge Sanz', 'jorge@academia.es', 28);
INSERT INTO cursos VALUES (10, 'SQL desde cero', 'BASICO', 40, 300, 1);
INSERT INTO cursos VALUES (11, 'Oracle avanzado', 'AVANZADO', 60, 550, 2);
INSERT INTO ediciones VALUES (100, 10, 1, DATE '2026-10-05', DATE '2026-11-27', 'PRESENCIAL', 'A1', 20);
INSERT INTO ediciones VALUES (101, 11, NULL, DATE '2027-01-11', DATE '2027-03-19', 'ONLINE', NULL, 25);
INSERT INTO empresas VALUES (1, 'B12345678', 'Talleres Martín S.L.');
INSERT INTO alumnos VALUES (1, '33333333P', 'Ana Gil', DATE '2001-05-14', 'ana.gil@correo.es');
INSERT INTO alumnos VALUES (2, '44444444A', 'Luis Mora', NULL, NULL);
INSERT INTO matriculas (alumno_id, edicion_id) VALUES (1, 100);
INSERT INTO matriculas (alumno_id, edicion_id, paga_alumno, empresa_id) VALUES (2, 100, 'N', 1);
COMMIT;
-- Casos que deben fallar
INSERT INTO cursos VALUES (12, 'Python', 'BASICO', 40, 300, 1); -- Elena ya coordina un curso
INSERT INTO ediciones VALUES (102, 10, 2, DATE '2027-02-01', DATE '2027-03-01', 'PRESENCIAL', NULL, 20);
INSERT INTO ediciones VALUES (103, 10, 2, DATE '2026-10-05', DATE '2027-03-01', 'PRESENCIAL', 'B2', 20);
INSERT INTO ediciones VALUES (104, 10, 2, DATE '2027-02-01', DATE '2027-03-01', 'PRESENCIAL', 'B2', 35);
INSERT INTO matriculas (alumno_id, edicion_id, paga_alumno) VALUES (1, 101, 'N');
INSERT INTO matriculas (alumno_id, edicion_id, nota) VALUES (2, 101, 10.5);
ORA-00001: unique constraint (CURSO.UQ_CUR_COORDINADOR) violated on table CURSO.CURSOS columns (COORDINADOR_ID)
ORA-02290: check constraint (CURSO.CK_EDI_AULA) violated
ORA-00001: unique constraint (CURSO.UQ_EDI_CURSO_INICIO) violated on table CURSO.EDICIONES columns (CURSO_ID, FECHA_INICIO)
ORA-02290: check constraint (CURSO.CK_EDI_PLAZAS) violated
ORA-02290: check constraint (CURSO.CK_MAT_PAGADOR) violated
ORA-02290: check constraint (CURSO.CK_MAT_NOTA) violated
(Se muestra solo la línea principal de cada error.) Y el ON DELETE SET NULL:
DELETE FROM profesores WHERE profesor_id = 2;
SELECT curso_id, nombre, coordinador_id FROM cursos;
ROLLBACK;
1 fila suprimida.
CURSO_ID NOMBRE COORDINADOR_ID
---------- -------------------- --------------
10 SQL desde cero 1
11 Oracle avanzado (nulo)
2 filas seleccionadas.
Rollback terminado.
Jorge no impartía ninguna edición (la 101 aún no tiene profesor), así que pudo borrarse; si hubiera tenido ediciones, fk_edi_profesor (NO ACTION) lo habría impedido.
Restricciones no declarativas de la academia: que el número de matrículas de una edición no supere sus plazas (requiere contar), que un profesor no imparta dos ediciones presenciales solapadas en fechas, que la fecha de matrícula sea anterior al fin de la edición (regla entre tablas: candidata a aserción en 26ai) y que la nota solo se ponga cuando la edición ha terminado (depende de la fecha actual).
19. Ejercicios
Ejercicios resueltos
Ejercicio 1: Poner nombre a las restricciones
Reescribe esta tabla para que todas sus restricciones tengan nombre según la convención del curso, usando restricciones en línea para NOT NULL y la PK, y fuera de línea para el resto. Comprueba el resultado en el diccionario.
CREATE TABLE cursos_demo (
curso_id NUMBER(4) PRIMARY KEY,
codigo VARCHAR2(10) NOT NULL UNIQUE,
titulo VARCHAR2(80) NOT NULL,
horas NUMBER(4) CHECK (horas > 0),
nivel VARCHAR2(8) CHECK (nivel IN ('BASICO', 'MEDIO', 'AVANZADO'))
);
Ver solución
CREATE TABLE cursos_demo (
curso_id NUMBER(4) CONSTRAINT pk_cursos_demo PRIMARY KEY,
codigo VARCHAR2(10) CONSTRAINT nn_cud_codigo NOT NULL,
titulo VARCHAR2(80) CONSTRAINT nn_cud_titulo NOT NULL,
horas NUMBER(4),
nivel VARCHAR2(8),
CONSTRAINT uq_cud_codigo UNIQUE (codigo),
CONSTRAINT ck_cud_horas CHECK (horas > 0),
CONSTRAINT ck_cud_nivel CHECK (nivel IN ('BASICO', 'MEDIO', 'AVANZADO'))
);
SELECT constraint_name, constraint_type, search_condition_vc
FROM user_constraints
WHERE table_name = 'CURSOS_DEMO'
ORDER BY constraint_type, constraint_name;
CONSTRAINT_NAME C SEARCH_CONDITION_VC
---------------- - ---------------------------------------------
CK_CUD_HORAS C horas > 0
CK_CUD_NIVEL C nivel IN ('BASICO', 'MEDIO', 'AVANZADO')
NN_CUD_CODIGO C "CODIGO" IS NOT NULL
NN_CUD_TITULO C "TITULO" IS NOT NULL
PK_CURSOS_DEMO P (nulo)
UQ_CUD_CODIGO U (nulo)
6 filas seleccionadas.
Ningún SYS_C. Si una regla se incumple, el error dirá exactamente cuál. Observa que codigo necesita dos restricciones (NOT NULL y UNIQUE) porque es una clave alternativa; y que horas y nivel siguen admitiendo nulos, porque un CHECK acepta los valores desconocidos.
Ejercicio 2: Predecir errores en EMPRESA
Sin ejecutarlas, indica qué sentencias fallarán, con qué código de error y por qué restricción. Después compruébalo (termina con ROLLBACK).
INSERT INTO departamentos VALUES (80, 'VENTAS', 'Málaga', 10000);
INSERT INTO productos VALUES (21, 'Hub USB', NULL, 4, 25, 10, NULL, 'X');
INSERT INTO lineas_pedido VALUES (5024, 2, 99, 1, 10, 0);
DELETE FROM clientes WHERE cliente_id = 1013;
DELETE FROM clientes WHERE cliente_id = 1001;
INSERT INTO empleados (emp_id, apellidos, email, fecha_alta, oficio, salario)
VALUES (300, 'Nadie', 'n@empresa.es', DATE '2026-10-01', 'BECARIO', 900);
UPDATE pedidos SET fecha_envio = DATE '2024-12-31' WHERE pedido_id = 5001;
UPDATE lineas_pedido SET descuento = NULL WHERE pedido_id = 5001 AND num_linea = 1;
ROLLBACK;
Ver solución
ORA-00001: unique constraint (CURSO.UQ_DEP_NOMBRE) violated on table CURSO.DEPARTAMENTOS columns (NOMBRE)
ORA-02290: check constraint (CURSO.CK_PRO_DESCATALOGADO) violated
ORA-02291: integrity constraint (CURSO.FK_LIN_PRODUCTO) violated - parent key not found
1 fila suprimida.
ORA-02292: integrity constraint (CURSO.FK_PED_CLIENTE) violated - child record found
ORA-01400: cannot insert NULL into ("CURSO"."EMPLEADOS"."NOMBRE")
ORA-02290: check constraint (CURSO.CK_PED_FECHAS) violated
ORA-01407: cannot update ("CURSO"."LINEAS_PEDIDO"."DESCUENTO") to NULL
Rollback terminado.
| Sentencia | Resultado | Motivo |
|---|---|---|
| Departamento 80 VENTAS | ORA-00001 | El nombre es clave alternativa y VENTAS ya existe (el 80 sí está libre) |
Producto con 'X' | ORA-02290 | descatalogado solo admite 'S' o 'N' |
| Línea con producto 99 | ORA-02291 | No existe el producto 99 |
| Borrar cliente 1013 | Correcto | No tiene pedidos |
| Borrar cliente 1001 | ORA-02292 | Tiene pedidos (5001, 5006, 5021) |
| Empleado sin nombre | ORA-01400 | nombre es NOT NULL y no se incluye en el INSERT |
| Envío anterior al pedido | ORA-02290 | ck_ped_fechas (el pedido 5001 es del 15/01/2025) |
| Descuento nulo | ORA-01407 | descuento es NOT NULL (su DEFAULT 0 no actúa en un UPDATE) |
El ROLLBACK deshace el único cambio que tuvo éxito (el borrado del cliente 1013): SELECT COUNT(*) FROM clientes; vuelve a dar 14.
Ejercicio 3: Citas de un centro de salud
Diseña e implementa las tablas medicos, pacientes y citas con estos requisitos: el número de colegiado del médico y el número de la Seguridad Social del paciente son únicos; cada paciente puede tener un médico de cabecera, y si el médico se da de baja, el paciente se queda sin médico asignado; una cita es de un paciente en una fecha y hora (no puede tener dos a la vez), con un médico obligatorio que tampoco puede tener dos citas a la misma hora; su estado es PROGRAMADA (por defecto), ATENDIDA o ANULADA; si se elimina un paciente se eliminan sus citas, pero no se puede eliminar un médico con citas. Pruébalo.
Ver solución
CREATE TABLE medicos (
medico_id NUMBER(5) CONSTRAINT pk_medicos PRIMARY KEY,
num_colegiado VARCHAR2(9) CONSTRAINT nn_med_colegiado NOT NULL,
nombre VARCHAR2(60) CONSTRAINT nn_med_nombre NOT NULL,
especialidad VARCHAR2(40) CONSTRAINT nn_med_especialidad NOT NULL,
CONSTRAINT uq_med_colegiado UNIQUE (num_colegiado)
);
CREATE TABLE pacientes (
paciente_id NUMBER(7) CONSTRAINT pk_pacientes PRIMARY KEY,
nss VARCHAR2(12) CONSTRAINT nn_pac_nss NOT NULL,
nombre VARCHAR2(60) CONSTRAINT nn_pac_nombre NOT NULL,
medico_cabecera_id NUMBER(5),
CONSTRAINT uq_pac_nss UNIQUE (nss),
CONSTRAINT fk_pac_cabecera FOREIGN KEY (medico_cabecera_id) REFERENCES medicos (medico_id)
ON DELETE SET NULL
);
CREATE TABLE citas (
paciente_id NUMBER(7),
fecha_hora DATE,
medico_id NUMBER(5) CONSTRAINT nn_cit_medico NOT NULL,
motivo VARCHAR2(200),
estado VARCHAR2(10) DEFAULT 'PROGRAMADA' CONSTRAINT nn_cit_estado NOT NULL,
CONSTRAINT pk_citas PRIMARY KEY (paciente_id, fecha_hora),
CONSTRAINT uq_cit_medico UNIQUE (medico_id, fecha_hora),
CONSTRAINT ck_cit_estado CHECK (estado IN ('PROGRAMADA', 'ATENDIDA', 'ANULADA')),
CONSTRAINT fk_cit_paciente FOREIGN KEY (paciente_id) REFERENCES pacientes (paciente_id) ON DELETE CASCADE,
CONSTRAINT fk_cit_medico FOREIGN KEY (medico_id) REFERENCES medicos (medico_id)
);
Pruebas:
INSERT INTO medicos VALUES (1, '461234567', 'Dra. Vidal', 'Medicina de familia');
INSERT INTO medicos VALUES (2, '467654321', 'Dr. Soler', 'Cardiología');
INSERT INTO pacientes VALUES (100, '280012345678', 'Rosa Prieto', 1);
INSERT INTO pacientes VALUES (101, '280099999999', 'Juan Gil', 1);
INSERT INTO citas (paciente_id, fecha_hora, medico_id)
VALUES (100, TO_DATE('2026-10-14 09:30', 'YYYY-MM-DD HH24:MI'), 2);
INSERT INTO citas (paciente_id, fecha_hora, medico_id) -- el Dr. Soler ya está ocupado
VALUES (101, TO_DATE('2026-10-14 09:30', 'YYYY-MM-DD HH24:MI'), 2);
DELETE FROM medicos WHERE medico_id = 2; -- tiene citas
DELETE FROM medicos WHERE medico_id = 1; -- solo es médico de cabecera
SELECT paciente_id, nombre, medico_cabecera_id FROM pacientes;
DELETE FROM pacientes WHERE paciente_id = 100;
SELECT COUNT(*) AS citas_restantes FROM citas;
ORA-00001: unique constraint (CURSO.UQ_CIT_MEDICO) violated on table CURSO.CITAS columns (MEDICO_ID, FECHA_HORA)
ORA-02292: integrity constraint (CURSO.FK_CIT_MEDICO) violated - child record found
1 fila suprimida.
PACIENTE_ID NOMBRE MEDICO_CABECERA_ID
----------- -------------------- ------------------
100 Rosa Prieto (nulo)
101 Juan Gil (nulo)
2 filas seleccionadas.
1 fila suprimida.
CITAS_RESTANTES
---------------
0
1 fila seleccionada.
Claves del diseño: la PK de citas (paciente + fecha y hora) impide que un paciente tenga dos citas simultáneas y la UNIQUE (medico_id, fecha_hora) lo impide para el médico; se usa DATE porque guarda la hora. Las tres acciones referenciales distintas reflejan tres requisitos distintos.
Ejercicio 4: Reglas con CHECK
Crea la tabla ofertas_demo (identificador, código, tipo, valor, fecha de inicio y fecha de fin opcional) con estas reglas: el código debe estar en mayúsculas y sin espacios; el tipo es PORCENTAJE o IMPORTE; si es porcentaje, el valor va de 1 a 90, y si es importe, debe ser positivo; la fecha de fin, si existe, no puede ser anterior a la de inicio. Prueba un caso válido y un caso que incumpla cada regla.
Ver solución
CREATE TABLE ofertas_demo (
oferta_id NUMBER(6) CONSTRAINT pk_ofertas_demo PRIMARY KEY,
codigo VARCHAR2(12) CONSTRAINT nn_ofe_codigo NOT NULL,
tipo VARCHAR2(10) CONSTRAINT nn_ofe_tipo NOT NULL,
valor NUMBER(8,2) CONSTRAINT nn_ofe_valor NOT NULL,
fecha_inicio DATE CONSTRAINT nn_ofe_inicio NOT NULL,
fecha_fin DATE,
CONSTRAINT ck_ofe_codigo CHECK (codigo = UPPER(codigo) AND codigo NOT LIKE '% %'),
CONSTRAINT ck_ofe_tipo CHECK (tipo IN ('PORCENTAJE', 'IMPORTE')),
CONSTRAINT ck_ofe_valor CHECK ((tipo = 'PORCENTAJE' AND valor BETWEEN 1 AND 90)
OR (tipo = 'IMPORTE' AND valor > 0)),
CONSTRAINT ck_ofe_fechas CHECK (fecha_fin >= fecha_inicio)
);
INSERT INTO ofertas_demo VALUES (1, 'BLACK2026', 'PORCENTAJE', 30, DATE '2026-11-27', DATE '2026-11-30');
INSERT INTO ofertas_demo VALUES (2, 'VERANO', 'IMPORTE', 150, DATE '2026-07-01', NULL);
INSERT INTO ofertas_demo VALUES (3, 'navidad', 'PORCENTAJE', 10, DATE '2026-12-01', NULL);
INSERT INTO ofertas_demo VALUES (4, 'MEGA', 'PORCENTAJE', 95, DATE '2026-12-01', NULL);
INSERT INTO ofertas_demo VALUES (5, 'REGALO', 'IMPORTE', 0, DATE '2026-12-01', NULL);
INSERT INTO ofertas_demo VALUES (6, 'RAPIDA', 'IMPORTE', 5, DATE '2026-12-01', DATE '2026-11-01');
INSERT INTO ofertas_demo VALUES (7, 'DOS PALABRAS', 'IMPORTE', 5, DATE '2026-12-01', NULL);
1 fila creada.
1 fila creada.
ORA-02290: check constraint (CURSO.CK_OFE_CODIGO) violated
ORA-02290: check constraint (CURSO.CK_OFE_VALOR) violated
ORA-02290: check constraint (CURSO.CK_OFE_VALOR) violated
ORA-02290: check constraint (CURSO.CK_OFE_FECHAS) violated
ORA-02290: check constraint (CURSO.CK_OFE_CODIGO) violated
ck_ofe_valor es una regla condicional: el rango válido de una columna depende del valor de otra, un caso típico de CHECK de varias columnas con OR. Como tipo y valor son NOT NULL, la condición nunca será desconocida. En cambio, ck_ofe_fechas acepta la oferta 2 porque su fecha_fin es nula (oferta sin fecha de fin).
Ejercicio 5: Mapa de claves ajenas
Escribe una consulta sobre el diccionario que muestre, para cada clave ajena de tu esquema: la tabla hija, la columna hija, la tabla padre, la columna padre referenciada y la regla de borrado. Debe funcionar también con claves compuestas.
Ver solución
COLUMN tabla_hija FORMAT A14
COLUMN columna_hija FORMAT A18
COLUMN tabla_padre FORMAT A14
COLUMN columna_padre FORMAT A14
COLUMN regla FORMAT A9
SELECT h.table_name AS tabla_hija,
hc.column_name AS columna_hija,
p.table_name AS tabla_padre,
pc.column_name AS columna_padre,
h.delete_rule AS regla
FROM user_constraints h
JOIN user_cons_columns hc ON hc.constraint_name = h.constraint_name
JOIN user_constraints p ON p.constraint_name = h.r_constraint_name
JOIN user_cons_columns pc ON pc.constraint_name = p.constraint_name
AND pc.position = hc.position
WHERE h.constraint_type = 'R'
ORDER BY h.table_name, hc.column_name;
TABLA_HIJA COLUMNA_HIJA TABLA_PADRE COLUMNA_PADRE REGLA
-------------- ------------------ -------------- -------------- ---------
ASIGNACIONES EMP_ID EMPLEADOS EMP_ID NO ACTION
ASIGNACIONES PROYECTO_ID PROYECTOS PROYECTO_ID CASCADE
CATEGORIAS CATEGORIA_PADRE_ID CATEGORIAS CATEGORIA_ID NO ACTION
CLIENTES COMERCIAL_ID EMPLEADOS EMP_ID NO ACTION
EMPLEADOS DEPT_ID DEPARTAMENTOS DEPT_ID NO ACTION
EMPLEADOS JEFE_ID EMPLEADOS EMP_ID NO ACTION
LINEAS_PEDIDO PEDIDO_ID PEDIDOS PEDIDO_ID CASCADE
LINEAS_PEDIDO PRODUCTO_ID PRODUCTOS PRODUCTO_ID NO ACTION
PEDIDOS CLIENTE_ID CLIENTES CLIENTE_ID NO ACTION
PEDIDOS EMPLEADO_ID EMPLEADOS EMP_ID NO ACTION
PRODUCTOS CATEGORIA_ID CATEGORIAS CATEGORIA_ID NO ACTION
PROYECTOS DEPT_ID DEPARTAMENTOS DEPT_ID NO ACTION
12 filas seleccionadas.
La consulta usa cuatro “copias” de las vistas: la restricción hija (h), sus columnas (hc), la restricción padre a la que apunta (p, a través de r_constraint_name) y las columnas de esta (pc). La condición pc.position = hc.position empareja la primera columna de la FK con la primera de la PK, la segunda con la segunda, etc.: así funciona con claves compuestas. Esta consulta es, en la práctica, el grafo relacional del esquema en forma de tabla.
Ejercicio 6: Departamentos y jefes circulares
Todo departamento tiene obligatoriamente un jefe, que es un empleado; todo empleado pertenece obligatoriamente a un departamento. Crea departamentos_d y empleados_d con FK DEFERRABLE INITIALLY IMMEDIATE y demuestra cómo insertar el departamento 1 (I+D) con su jefa Carla (empleada 10) y otro empleado, Bruno.
Ver solución
CREATE TABLE departamentos_d (
dept_id NUMBER(4) CONSTRAINT pk_departamentos_d PRIMARY KEY,
nombre VARCHAR2(30) CONSTRAINT nn_ddd_nombre NOT NULL,
jefe_id NUMBER(6) CONSTRAINT nn_ddd_jefe NOT NULL
);
CREATE TABLE empleados_d (
emp_id NUMBER(6) CONSTRAINT pk_empleados_d PRIMARY KEY,
nombre VARCHAR2(30) CONSTRAINT nn_edd_nombre NOT NULL,
dept_id NUMBER(4) CONSTRAINT nn_edd_dept NOT NULL,
CONSTRAINT fk_edd_dept FOREIGN KEY (dept_id) REFERENCES departamentos_d (dept_id)
DEFERRABLE INITIALLY IMMEDIATE
);
ALTER TABLE departamentos_d ADD CONSTRAINT fk_ddd_jefe
FOREIGN KEY (jefe_id) REFERENCES empleados_d (emp_id)
DEFERRABLE INITIALLY IMMEDIATE;
INSERT INTO departamentos_d VALUES (1, 'I+D', 10); -- sin diferir: falla
ERROR en línea 1:
ORA-02291: integrity constraint (CURSO.FK_DDD_JEFE) violated - parent key not found
SET CONSTRAINTS fk_edd_dept, fk_ddd_jefe DEFERRED;
INSERT INTO departamentos_d VALUES (1, 'I+D', 10);
INSERT INTO empleados_d VALUES (10, 'Carla', 1);
INSERT INTO empleados_d VALUES (11, 'Bruno', 1);
COMMIT;
SELECT d.nombre AS departamento, e.nombre AS jefe
FROM departamentos_d d JOIN empleados_d e ON e.emp_id = d.jefe_id;
1 fila creada.
1 fila creada.
1 fila creada.
Confirmación terminada.
DEPARTAMENTO JEFE
------------------------------ ------------------------------
I+D Carla
1 fila seleccionada.
La segunda FK se añade con ALTER TABLE porque, al crear departamentos_d, la tabla empleados_d aún no existía. Con INITIALLY IMMEDIATE, las operaciones normales (dar de alta un empleado en un departamento existente) se comprueban al instante, y solo la transacción que lo necesita difiere las restricciones. Para borrar las tablas: ALTER TABLE departamentos_d DROP CONSTRAINT fk_ddd_jefe; y después los dos DROP TABLE.
Ejercicio 7: Implementar un fragmento de E/R
Implementa este fragmento de diseño: una SEDE tiene exactamente un DIRECTOR y cada director dirige exactamente una sede; cada CONTRATO pertenece a una sede (obligatorio) y lo firma o un PARTICULAR (identificado por NIF) o una EMPRESA (identificada por CIF), exactamente uno de los dos. Indica qué parte del diseño no queda garantizada.
Ver solución
CREATE TABLE sedes_demo (
sede_id NUMBER(4) CONSTRAINT pk_sedes_demo PRIMARY KEY,
ciudad VARCHAR2(40) CONSTRAINT nn_sed_ciudad NOT NULL
);
CREATE TABLE directores_demo (
director_id NUMBER(6) CONSTRAINT pk_directores_demo PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_dir_nombre NOT NULL,
sede_id NUMBER(4) CONSTRAINT nn_dir_sede NOT NULL
CONSTRAINT uq_dir_sede UNIQUE
CONSTRAINT fk_dir_sede REFERENCES sedes_demo (sede_id)
);
CREATE TABLE particulares_demo (nif VARCHAR2(9) CONSTRAINT pk_particulares_demo PRIMARY KEY);
CREATE TABLE empresas_demo (cif VARCHAR2(9) CONSTRAINT pk_empresas_demo PRIMARY KEY);
CREATE TABLE contratos_demo (
contrato_id NUMBER(8) CONSTRAINT pk_contratos_demo PRIMARY KEY,
sede_id NUMBER(4) CONSTRAINT nn_ctr_sede NOT NULL
CONSTRAINT fk_ctr_sede REFERENCES sedes_demo (sede_id),
particular_nif VARCHAR2(9) CONSTRAINT fk_ctr_particular REFERENCES particulares_demo (nif),
empresa_cif VARCHAR2(9) CONSTRAINT fk_ctr_empresa REFERENCES empresas_demo (cif),
CONSTRAINT ck_ctr_titular CHECK (
(CASE WHEN particular_nif IS NOT NULL THEN 1 ELSE 0 END) +
(CASE WHEN empresa_cif IS NOT NULL THEN 1 ELSE 0 END) = 1)
);
Las pruebas (sección 16.2 y 16.3) dan ORA-00001 (UQ_DIR_SEDE) para un segundo director en la misma sede y ORA-02290 (CK_CTR_TITULAR) para contratos con los dos titulares o con ninguno.
Lo que no queda garantizado: que toda sede tenga director (la cardinalidad mínima 1 en el lado de SEDE). Se podría insertar una sede y no darle nunca director. En 26ai podría resolverse con una aserción diferible del tipo ALL (SELECT s.sede_id FROM sedes_demo s) s SATISFY (EXISTS (SELECT 'x' FROM directores_demo d WHERE d.sede_id = s.sede_id)); en versiones anteriores, con un disparador o en la aplicación.
Ejercicio 8: ¿Declarativa o no?
Para cada regla de negocio, indica cómo la implementarías en Oracle (restricción concreta) o si no puede declararse con restricciones de tabla: a) el precio de un producto no puede ser negativo; b) un empleado no puede ganar más que su jefe; c) la comisión no puede superar el 50 % del salario; d) un cliente no puede tener más de 5 pedidos pendientes; e) el correo de un cliente, si existe, es único; f) la fecha de alta de un empleado no puede ser futura; g) un producto descatalogado debe tener stock 0; h) todo pedido debe tener al menos una línea; i) un pedido no puede asignarse a un empleado que no sea VENDEDOR.
Ver solución
| Regla | Implementación | Tipo |
|---|---|---|
| a) Precio no negativo | CHECK (precio >= 0) | Declarativa (ya existe: ck_pro_precio) |
| b) Salario no mayor que el del jefe | Compara filas distintas de la misma tabla. Necesita un disparador (PL/SQL); como aserción en 26ai sería una autocombinación de EMPLEADOS, que no cumple los requisitos de validación rápida | No declarativa |
| c) Comisión hasta el 50 % del salario | CHECK (comision <= salario * 0.5) | Declarativa (varias columnas de la misma fila) |
| d) Máximo 5 pedidos pendientes | Requiere contar filas: ni CHECK ni aserción (no admiten agregados). Disparador | No declarativa |
| e) Correo único si existe | UNIQUE (email) sin NOT NULL | Declarativa |
| f) Fecha de alta no futura | CHECK no admite SYSDATE (ORA-02436). Disparador o aplicación | No declarativa |
| g) Descatalogado implica stock 0 | CHECK (descatalogado = 'N' OR stock = 0) | Declarativa (implicación lógica: “no A o B”) |
| h) Todo pedido tiene al menos una línea | Cardinalidad mínima 1 en el lado “muchos”: aserción diferible en 26ai (ALL ... SATISFY (EXISTS ...)) o disparador | No declarativa con restricciones de tabla |
| i) El empleado de un pedido debe ser VENDEDOR | Depende de un dato de otra tabla: aserción en 26ai (NOT EXISTS con la combinación de PEDIDOS y EMPLEADOS en el WHERE) o disparador | No declarativa con restricciones de tabla |
La regla g) muestra un truco muy útil: la implicación “si A entonces B” se escribe en un CHECK como NOT A OR B.
Ejercicio 9: Orden de creación y de borrado
Te dan un script que crea, en este orden, las tablas lineas_factura (FK a facturas y a articulos), facturas (FK a clientes), articulos (FK a familias), clientes y familias. a) ¿Qué ocurre al ejecutarlo? b) Escribe un orden de creación válido. c) Escribe el orden de borrado sin usar CASCADE CONSTRAINTS. d) ¿Qué técnica permite crear las tablas en cualquier orden?
Ver solución
a) Fallará la creación de lineas_factura con ORA-00942: table or view does not exist, porque sus FK hacen referencia a tablas que todavía no existen; lo mismo ocurrirá con facturas y articulos. Solo se crearán clientes y familias.
b) Un orden válido es cualquiera en el que cada padre vaya antes que sus hijos (un orden topológico del grafo de dependencias):
clientes → familias → facturas → articulos → lineas_factura
(clientes y familias no dependen de nadie; facturas depende de clientes; articulos de familias; lineas_factura de ambas.)
c) El inverso: lineas_factura, articulos, facturas, familias, clientes (o cualquier orden en el que cada hija se borre antes que sus padres).
d) Crear primero todas las tablas sin claves ajenas y añadir después las FK con ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY. Es lo que hacen las herramientas de modelado al generar el DDL, y la única forma posible cuando hay referencias circulares (ejercicio 6). Para el borrado, DROP TABLE ... CASCADE CONSTRAINTS elimina la dependencia del orden.
Ejercicios propuestos
Ejercicio 10: Liga deportiva
Implementa con todas sus restricciones (con nombre) una base de datos para una liga: equipos (nombre único, ciudad, año de fundación entre 1850 y el año actual escrito como literal), jugadores (dorsal único dentro de cada equipo entre 1 y 99, posición de una lista cerrada, equipo opcional para los agentes libres), partidos (equipo local y visitante distintos, jornada, fecha, goles no negativos, y un equipo no puede jugar dos partidos en la misma jornada). Prueba cada restricción con un caso que la incumpla.
Ejercicio 11: Estados de restricciones en una carga
Crea una copia de productos con CTAS, añádele una clave primaria y un CHECK (stock >= 0). Desactiva el CHECK, inserta tres productos con stock negativo y documenta todo el proceso hasta dejar la restricción en ENABLE VALIDATE usando EXCEPTIONS INTO.
Ejercicio 12: Tabla de códigos frente a CHECK
El estado de los pedidos de EMPRESA se controla con CHECK (estado IN (...)). Diseña una alternativa con una tabla estados_pedido y una FK, otra con un dominio de enumeración (23ai) y compara las tres soluciones: facilidad para añadir un estado nuevo, posibilidad de guardar una descripción de cada estado, rendimiento y compatibilidad con 19c.
Ejercicio 13: Videoclub en streaming
Diseña el E/R, el esquema relacional y el script DDL de una plataforma de vídeo: usuarios (correo único), planes de suscripción, perfiles de cada usuario (entidad débil, máximo de perfiles no declarativo), contenidos que pueden ser películas o series (jerarquía exclusiva y total), episodios de las series (entidad débil con temporada y número) y visualizaciones (perfil, contenido, fecha y minuto en que se dejó). Indica qué restricciones no son declarativas.
Ejercicio 14: Analizar un esquema ajeno
Ejecuta la consulta del ejercicio 5 y otra sobre USER_CONSTRAINTS de tipo C para el esquema de la biblioteca (sección 18.1) y dibuja con Mermaid (erDiagram) el diagrama físico resultante. Comprueba que coincide con el esquema relacional propuesto.
Ejercicio 15: Nulos y restricciones
Diseña una batería de diez INSERT sobre una tabla de prueba con una PK, una UNIQUE compuesta, una FK compuesta y un CHECK, en la que la mitad de los casos involucren valores nulos. Predice el resultado de cada uno, ejecútalos y explica las sorpresas.
Ejercicio 16: Ampliar EMPRESA (en una copia)
Sin modificar EMPRESA, crea con CTAS copias de empleados y departamentos y añádeles (con ALTER TABLE ... ADD CONSTRAINT) las restricciones necesarias para estas reglas: la comisión no supera el 50 % del salario; solo los vendedores pueden tener comisión; el presupuesto de un departamento, si existe, es al menos de 10.000 €. ¿Cumplen los datos actuales todas las reglas? Usa EXCEPTIONS INTO para averiguarlo.
Ejercicio 17: Aserciones (Oracle 26ai)
En un usuario de pruebas con Oracle 26ai, escribe y prueba aserciones para dos reglas de EMPRESA: a) la fecha de envío de un pedido no puede ser anterior a la fecha de alta del cliente; b) todo proyecto debe tener al menos un empleado asignado (¿por qué tiene que ser diferible? ¿la cumplen los datos actuales?). Intenta después escribir como aserción “un departamento no puede tener más de 8 empleados” y explica el error.
Resumen
| Concepto | Sintaxis / clave |
|---|---|
| Restricción con nombre | CONSTRAINT nombre tipo ... (prefijos pk_, uq_, fk_, ck_, nn_); sin nombre: SYS_Cnnnnnn |
| En línea / fuera de línea | Junto a la columna / al final con (columnas); NOT NULL solo en línea; varias columnas solo fuera |
NOT NULL | col tipo CONSTRAINT nn_x NOT NULL → ORA-01400 / ORA-01407; '' es nulo |
PRIMARY KEY | Única + no nula, una por tabla, crea índice único → ORA-00001 |
UNIQUE | Claves alternativas; admite varios NULL; compuesta con nulos parciales sí cuenta como duplicado |
FOREIGN KEY | REFERENCES padre (col); apunta a PK o UNIQUE; admite NULL → ORA-02291 / ORA-02292 |
| Acciones | NO ACTION (defecto), ON DELETE CASCADE, ON DELETE SET NULL; no hay ON UPDATE |
CHECK | Condición sobre la fila; acepta si es desconocida (NULL); sin SYSDATE, subconsultas ni otras tablas → ORA-02290 |
DEFAULT | No es restricción: solo propone un valor en el INSERT |
| Estados | ENABLE/DISABLE (datos nuevos) × VALIDATE/NOVALIDATE (datos existentes); EXCEPTIONS INTO |
| Diferibles | DEFERRABLE INITIALLY IMMEDIATE \| DEFERRED; SET CONSTRAINTS ... DEFERRED \| IMMEDIATE; fallo en COMMIT → ORA-02091 |
| Diccionario | USER_CONSTRAINTS (tipos P, U, R, C), USER_CONS_COLUMNS, SEARCH_CONDITION_VC, R_CONSTRAINT_NAME, DELETE_RULE |
| Dominios (23ai) | CREATE DOMAIN d AS tipo [DEFAULT ...] CONSTRAINT ... CHECK (...); AS ENUM (...); DOMAIN_DISPLAY |
| E/R → SQL | 1:N con FK (NOT NULL si obligatoria); 1:1 con FK UNIQUE; N:M con tabla y PK compuesta; débil con ON DELETE CASCADE; arco con CHECK |
| No declarativas | Mínimos en el lado “muchos”, máximos concretos, totalidad, reglas entre tablas y con fecha actual → aserciones (26ai, sin agregados) o disparadores (PL/SQL) |
| Aserciones (26ai) | CREATE ASSERTION a CHECK (NOT EXISTS (...) \| ALL (...) x SATISFY (...)) [DEFERRABLE ...] → ORA-08601 |