1.
Lenguaje de Definición de Datos
1.2. Tablas en Oracle
1.1 Crear una tabla
A continuación se muestra una versión simplificada de la sintaxis necesaria para la
creación de una tabla en Oracle.
Para una definición más exhaustiva, puede consultar la sintaxis de creación de
tablas en la documentación oficial de Oracle.
CREATE TABLE tbl_name
(create_definition,...)
[table_options]
create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name,...) reference_definition
| CHECK (expr)
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[GENERATED BY DEFAULT AS IDENTITY] [UNIQUE [KEY] | [PRIMARY] KEY]
reference_definition:
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
1.1.1 Restricciones sobre las columnas de la tabla
Podemos aplicar las siguientes restricciones sobre las columnas de la tabla:
NOT NULL o NULL: Indica si la columna permite almacenar valores nulos o no.
DEFAULT: Permite indicar un valor inicial por defecto si no especificamos ninguno
en la inserción.
GENERATED BY DEFAULT AS IDENTITY: Sirve para indicar que es una columna
autonumérica. Su valor se incrementa automáticamente en cada inserción de una fila.
Se utiliza solo en campos numéricos.
UNIQUE KEY: Indica que el valor de la columna es único y no pueden aparecer dos
valores iguales en la misma columna.
PRIMARY KEY: Para indicar que una columna o varias son clave primaria.
CHECK: Nos permite realizar restricciones sobre una columna.
Ejemplo 1:
CREATE TABLE categoria (
idcategoria INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nombre VARCHAR(100) NOT NULL
);
CREATE TABLE pieza (
idpieza INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
color VARCHAR(50) NOT NULL,
precio NUMERIC(7,2) NOT NULL CHECK (precio > 0),
idcategoria INTEGER NOT NULL,
FOREIGN KEY (idcategoria) REFERENCES categoria(idcategoria)
);
Ejemplo 2:
CREATE TABLE turista (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nombre VARCHAR(50) NOT NULL,
apellidos VARCHAR(100) NOT NULL,
direccion VARCHAR(100) NOT NULL,
telefono VARCHAR(9) NOT NULL
);
CREATE TABLE hotel (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nombre VARCHAR(50) NOT NULL,
direccion VARCHAR(100) NOT NULL,
ciudad VARCHAR(25) NOT NULL,
plazas INTEGER NOT NULL,
telefono VARCHAR(9) NOT NULL
);
CREATE TABLE reserva (
id_turista INT NOT NULL,
id_hotel INT NOT NULL,
fecha_entrada DATETIME NOT NULL,
fecha_salida DATETIME NOT NULL,
regimen VARCHAR(2),
PRIMARY KEY (id_turista,id_hotel),
FOREIGN KEY (id_turista) REFERENCES turista(id),
FOREIGN KEY (id_hotel) REFERENCES hotel(id),
);
1.1.2 Opciones en la declaración de claves ajenas (FOREIGN KEY)
ON DELETE y ON UPDATE: Nos permiten indicar el efecto que provoca el borrado o la
actualización de los datos que están referenciados por claves ajenas. Por defecto,
se impide actualizar o eliminar las filas que tienen valores referenciados. Las
opciones que podemos especificar son las siguientes:
CASCADE: Permite actualizar o eliminar las filas que tienen valores referenciados
por claves ajenas.
SET NULL: Asigna el valor NULL a las filas que tienen valores referenciados por
claves ajenas.
DO NOTHING: No hace nada.
SET DEFAULT: Emplear valor por defecto a establecer.
Para gestionar el borrado referenciado de las claves ajenas, Oracle solo dispone de
las políticas ON DELETE CASCADE y ON DELETE SET NULL.
Ejemplo 1:
DROP TABLE pieza;
DROP TABLE categoria;
CREATE TABLE categoria (
idcategoria INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nombre VARCHAR(100) NOT NULL
);
CREATE TABLE pieza (
idpieza INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
color VARCHAR(50) NOT NULL,
precio DECIMAL(7,2) NOT NULL,
idcategoria INT NOT NULL,
FOREIGN KEY (idcategoria) REFERENCES categoria(idcategoria)
);
INSERT INTO categoria VALUES (1, 'Categoria A');
INSERT INTO categoria VALUES (2, 'Categoria B');
INSERT INTO categoria VALUES (3, 'Categoria C');
INSERT INTO pieza VALUES (1, 'Pieza 1', 'Blanco', 25.90, 1);
INSERT INTO pieza VALUES (2, 'Pieza 2', 'Verde', 32.75, 1);
INSERT INTO pieza VALUES (3, 'Pieza 3', 'Rojo', 12.00, 2);
INSERT INTO pieza VALUES (4, 'Pieza 4', 'Azul', 24.50, 2);
¿Podríamos borrar la Categoría A de la tabla categoria?
¿Y la Categoría C?
¿Podríamos actualizar la Categoría A de la tabla categoria?
Ejemplo 2:
DROP TABLE pieza;
DROP TABLE categoria;
CREATE TABLE categoria (
idcategoria INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nombre VARCHAR(100) NOT NULL
);
CREATE TABLE pieza (
idpieza INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
color VARCHAR(50) NOT NULL,
precio DECIMAL(7,2) NOT NULL,
idcategoria INT NOT NULL,
FOREIGN KEY (idcategoria) REFERENCES categoria(idcategoria) ON DELETE CASCADE ON
UPDATE CASCADE
);
INSERT INTO categoria VALUES (1, 'Categoria A');
INSERT INTO categoria VALUES (2, 'Categoria B');
INSERT INTO categoria VALUES (3, 'Categoria C');
INSERT INTO pieza VALUES (1, 'Pieza 1', 'Blanco', 25.90, 1);
INSERT INTO pieza VALUES (2, 'Pieza 2', 'Verde', 32.75, 1);
INSERT INTO pieza VALUES (3, 'Pieza 3', 'Rojo', 12.00, 2);
INSERT INTO pieza VALUES (4, 'Pieza 4', 'Azul', 24.50, 2);
¿Podríamos borrar la Categoría A de la tabla categoria?
¿Qué le ocurre a las piezas que pertenecen la Categoría A después de borrarla?
¿Podríamos actualizar la Categoría A de la tabla categoria?
¿Qué le ocurre a las piezas que pertenecen la Categoría A después de actualizarla?
Ejemplo 3:
DROP DATABASE IF EXISTS proveedores;
CREATE DATABASE proveedores CHARSET utf8mb4;
USE proveedores;
CREATE TABLE categoria (
codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(100) NOT NULL
);
CREATE TABLE pieza (
codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
color VARCHAR(50) NOT NULL,
precio DECIMAL(7,2) NOT NULL,
codigo_categoria INT UNSIGNED,
FOREIGN KEY (codigo_categoria) REFERENCES categoria(codigo)
ON DELETE SET NULL
ON UPDATE SET NULL
);
INSERT INTO categoria VALUES (1, 'Categoria A');
INSERT INTO categoria VALUES (2, 'Categoria B');
INSERT INTO categoria VALUES (3, 'Categoria C');
INSERT INTO pieza VALUES (1, 'Pieza 1', 'Blanco', 25.90, 1);
INSERT INTO pieza VALUES (2, 'Pieza 2', 'Verde', 32.75, 1);
INSERT INTO pieza VALUES (3, 'Pieza 3', 'Rojo', 12.00, 2);
INSERT INTO pieza VALUES (4, 'Pieza 4', 'Azul', 24.50, 2);
¿Podríamos borrar la Categoría A de la tabla categoria?
¿Qué le ocurre a las piezas que pertenecen la Categoría A después de borrarla?
¿Podríamos actualizar la Categoría A de la tabla categoria?
¿Qué le ocurre a las piezas que pertenecen la Categoría A después de actualizarla?
