Creación de tablas

Creación, Modificación y Eliminación de tablas

En este apartado veremos los comandos SQL que se utilizarán para crear y modificar la definición de una tabla, así como para eliminarla de la base de datos.

Creación de Tablas

El nombre de las tablas debe cumplir las siguientes reglas:

  • Deben comenzar con una letra
  • No deben tener más de 30 caracteres
  • Sólo se permiten utilizar letras del alfabeto (inglés), números o el signo de subrayado (también el signo $ y #, pero esos se utilizan de manera especial por lo que no son recomendados)
  • No puede haber dos tablas con el mismo nombre para el mismo usuario (pueden coincidir los nombres si están en distintos esquemas)
  • No puede coincidir con el nombre de una palabra reservada de SQL

Para la creación de tablas con SQL se utiliza el comando CREATE TABLE. Este comando tiene una sintaxis más compleja de la que aquí se expone, pero vamos a comenzar por la sintaxis básica. Sintaxis básica de creación de tablas:

CREATE TABLE nombre_tabla (
  columna1  tipo_dato  [ restricciones de columna1 ],
  columna2  tipo_dato  [ restricciones de columna2 ],
  columna3  tipo_dato  [ restricciones de columna3 ],
  ...
  [ restricciones de tabla ]
);

Para realizar las separaciones se utiliza la coma. La última línea, antes del paréntesis de cierre, no lleva coma.

Donde las restricciones de columna tienen la siguiente sintaxis:

CONSTRAINT nombre_restricción {
  [NOT] NULL | UNIQUE | PRIMARY KEY | DEFAULT valor | CHECK (condición)
}

Y las restricciones de tabla tienen la siguiente sintaxis:

CONSTRAINT nombre_restricción {
  PRIMARY KEY (columna1 [,columna2] ... )
| UNIQUE (columna1 [,columna2] ... )
| FOREIGN KEY (columna1 [,columna2] ... )
    REFERENCES nombre_tabla (columna1 [,columna2] ... )
    [ON DELETE {CASCADE | SET NULL}]
| CHECK (condición)
}

Obligatoriamente debemos crear una restricción de tabla cuando una misma restricción afecte a varias columnas. Por ejemplo si tenemos una clave primaria compuesta por varios campos, debemos establecer una restricción de tabla, no de columna.

El significado de las distintas opciones que aparecen en la sintaxis CREATE TABLE es:

  • PRIMARY KEY: establece ese atributo o conjunto de atributos como la clave primaria de la tabla. Esta restricción ya implica las restricciones UNIQUE y NOT NULL.
  • UNIQUE: impide que se introduzcan valores repetidos para ese atributo o conjunto de atributos. No se puede utilizar junto con PRIMARY KEY. Se utiliza para claves alternativas.
  • NOT NULL: evita que se introduzcan filas en la tabla con valor NULL para ese atributo. No se utiliza con PRIMARY KEY.
  • DEFAULT valor_por_defecto: permite asignar un valor por defecto al campo que se está definiendo.
  • CHECK (condición): permite establecer condiciones que deben cumplir los valores de la tabla que se introducirán en dicha columna.
    • Si un CHECK se especifica como una restricción de columna, la condición sólo se puede referir a esa columna.
    • Si el CHECK se especifica como restricción de tabla, la condición puede afectar a todas las columnas de la tabla.
    • Sólo se permiten condiciones simples, por ejemplo, no está permitido referirse a columnas de otras tablas o formular subconsulas dentro de un CHECK.
    • Además las funciones SYSDATE y USER no se pueden utilizar dentro de la condición. En principio están permitidas comparaciones simples de atributos y operadores lógicos (AND, OR y NOT).
  • FOREIGN KEY: define una clave externa de la tabla respecto de otra tabla. Esta restricción especifica una columna o una lista de columnas como clave externa de una tabla referenciada. No se puede definir una restricción de integridad referencial que se refiere a una tabla antes de que dicha tabla haya sido creada. Es importante resaltar que una clave externa debe referenciar a una clave primaria completa de la tabla padre, y nunca a un subconjunto de los atributos que forman esta clave primaria.
    • ON DELETE CASCADE: especifica que se mantenga automáticamente la integridad referencial borrando los valores de la llave externa correspondientes a un valor borrado de la tabla referenciada (tabla padre). Si se omite esta opción no se permitirá borrar valores de una tabla que sean referenciados como llave externa en otras tablas.
    • ON DELETE SET NULL: especifica que se ponga a NULL los valores de la llave externa correspondientes a un valor borrado de la tabla referenciada (tabla padre).

Nota

En Oracle, el valor predeterminado es que las filas de la tabla principal no se pueden eliminar si existe una fila en la tabla secundaria que se refiere a esta fila principal, si no indicamos ON DELETE CASCADE o ON DELETE SET NULL. El estándar SQL define muchas más opciones.

Nota

El estándar SQL define 5 opciones para manejar esta situación de tablas principal/secundaria de varias maneras. Estas opciones son:

  • ON DELETE CASCADE: si se elimina una fila de la tabla principal, se eliminan todas las filas coincidentes en la tabla secundaria.
  • ON DELETE SET NULL: si se elimina una fila de la tabla principal, todas las columnas de referencia en todas las filas coincidentes de la tabla secundaria se establecen en NULL.
  • ON DELETE SET DEFAULT: si se elimina una fila de la tabla principal, todas las columnas de referencia en todas las filas coincidentes de la tabla secundaria se configuran en el valor predeterminado de la columna.
  • ON DELETE RESTRICT: está prohibido eliminar una fila de la tabla principal si esa fila tiene alguna fila coincidente en la tabla secundaria. El punto en el tiempo cuando realiza la comprobación se puede aplazar hasta que se realice COMMIT.
  • ON DELETE NO ACTION (el valor predeterminado): se prohíbe eliminar una fila de la tabla primaria si esa fila tiene filas coincidentes en la tabla secundaria.

Análoga a la opción ON DELETE hay una opción ON UPDATE. Define las mismas 5 opciones para el caso de cambiar una columna en la tabla principal a la que hace referencia la columna de una tabla secundaria.

  • ON UPDATE CASCADE: Cualquier cambio en una columna referenciada en la tabla primaria provoca el mismo cambio en la columna de referencia correspondiente en las filas coincidentes de la tabla secundaria.
  • ON UPDATE SET NULL: Cualquier cambio en una columna referenciada en la tabla primaria provoca que la columna de referencia correspondiente en las filas coincidentes de la tabla secundaria se establezca como nula.
  • ON UPDATE SET DEFAULT: Cualquier cambio en una columna referenciada en la tabla principal provoca que la columna de referencia correspondiente en las filas coincidentes de la tabla de secundaria se establezca en su valor predeterminado.
  • ON UPDATE RESTRICT: está prohibido cambiar una fila de la tabla principal si esa fila tiene filas coincidentes en la tabla secundaria. El punto en el tiempo cuando se realiza la comprobación se puede aplazar hasta que se realice COMMIT.
  • ON UPDATE NO ACTION (valor predeterminado): está prohibido cambiar una fila de la tabla principal si esa fila tiene alguna fila coincidente en la tabla secundaria.

Si ON DELETE o ON UPDATE no están especificados, se producirá la acción predeterminada NO ACTION. En algunos sistemas, NO ACTION se implementa en el sentido de la opción RESTRICT.

En la definición de una tabla pueden aparecer varias cláusulas FOREIGN KEY, tantas como llaves externas tenga la tabla, sin embargo sólo puede existir una llave primaria, si bien esta llave primaria puede estar formada por varios atributos.

La utilización de la cláusula CONSTRAINT nombre_restricción establece un nombre determinado para la restricción de integridad, lo cual permite buscar en el Diccionario de Datos de la base de datos con posterioridad y fácilmente las restricciones introducidas para una determinada tabla.

Ejemplo en MySQL/MariaDB:

Crear la tabla Coches (matricula, marca, modelo, nuevo, precio, fecha_matriculacion)

La matricula será la clave principal.

La marca no puede estar vacía.

El modelo no se puede repetir.

El campo nuevo indica si el coche es nuevo o de segunda mano y, por defecto, será true.

El precio tendrá un valor comprendido entre 1.000 y 30.000 €.

CREATE TABLE coches(
 
);

Otro ejemplo en Oracle:

CREATE TABLE usuarios (
 id NUMBER PRIMARY KEY,
 dni CHAR(9) UNIQUE,
 nombre VARCHAR2(50) NOT NULL,
 edad NUMBER CHECK (edad >= 0 and edad < 120)
);

En el caso anterior no hemos asignado nombre a las restricciones, así que Oracle le asignará un nombre de la forma SYS_Cn, donde n es un número. Esta forma no es recomendable puesto que si deseamos modificar posteriormente el diseño de la tabla nos será muy difícil gestionar las restricciones.Otra forma más adecuada es dando nombre a las restricciones. Un ejemplo en Oracle:

CREATE TABLE usuarios (
 id NUMBER CONSTRAINT usu_id_pk PRIMARY KEY,
 dni CHAR(9) CONSTRAINT usu_dni_uq UNIQUE,
 nombre VARCHAR2(50) CONSTRAINT usu_nom_nn NOT NULL,
 edad NUMBER CONSTRAINT usu_edad_ck CHECK (edad >= 0 and edad < 120) 
);

Más información:

Lenguaje de definición de datos (DDL)

Deja un comentario