U3 Tema 2 Creación de esquemas de Bases de Datos

Lenguaje de definición de datos

Un lenguaje de definición de datos (Data Definition Language,

DDL por sus siglas en inglés) es un lenguaje proporcionado por
el sistema de gestión de base de datos que permite a los usuarios
de la misma llevar a cabo las tareas de definición de las estructuras
que almacenarán los datos así como de los procedimientos o
funciones que permitan consultarlos.

La definición de la estructura de la base de datos incluye tanto la

creación inicial de los diferentes objetos que formarán la base de
datos, como el mantenimiento de esa estructura. Las sentencias
del DDL utilizan unos verbos que se repiten para los distintos
objetos. Por ejemplo para crear un objeto nuevo el verbo será
CREATE y a continuación el tipo de objeto a crear. CREATE
DATABASE es la sentencia para crear una base de datos, CREATE
TABLE nos permite crear una nueva tabla, CREATE INDEX crear un
nuevo índice.

Las principales funcionalidades de SQL como lenguaje de

definición (DDL) son la creación, modificación y borrado de las
tablas que componen la base de datos, así como de los índices,
vistas, sinónimos, permisos, etc. que pudieran definirse sobre las
mismas.

CREATE DATABASE

CREATE DATABASE nbBasedeDatos

[ ON
 [ PRIMARY ] [ <esp_fichero> [ ,...n ]

[ , <grupo> [ ,...n ] ]

[ LOG ON { < esp_fichero > [ ,...n ] } ]

[ COLLATE nbintercalacion]

[ WITH <external_access_option> ]

[;]

Como vemos la instrucción mínima es: CREATE

DATABASE nbBasedeDatos

nbBasedeDatos: Es el nombre de la nueva base de datos. Los

nombres de base de datos deben ser únicos en una instancia de
SQL Server y cumplir las reglas de los identificadores. Puede tener
128 caracteres como máximo, excepto en un caso que veremos
más adelante.

CREATE DATABASE crea una base de datos con el nombre dado.

Para usar CREATE DATABASE, necesita el permiso CREATE en la
base de datos.

Sentencia CREATE TABLE

Un comando CREATE muy común es el CREATE TABLE. El uso

típico es:

CREATE [TEMPORARY] TABLE [nombre de la tabla] ( [definiciones

de columna] ) [parámetros de la tabla].
¿Qué es PRIMARY KEY en SQL Server y cómo funciona?

Una llave primaria o PRIMARY KEY es una columna o

un grupo de columnas que identifica de forma exclusiva
cada fila de una tabla. Puede crear una llave primaria para
una tabla utilizando la restricción PRIMARY KEY.

Si la llave principal consta de solo una columna, puede

definir el uso de la restricción PRIMARY KEY como una
restricción de columna:
CREATE TABLE table_name (
pk_column data_type PRIMARY KEY,
...
);
En caso de que la llave primaria tenga dos o más
columnas, debe usar la restricción PRIMARY KEY como
una restricción de tabla:
CREATE TABLE table_name (
pk_column_1 data_type,
pk_column_2 data type,
...
PRIMARY KEY (pk_column_1, pk_column_2)
);
Cada tabla puede contener solo una clave primaria. Todas
las columnas que participan en la llave primaria deben
definirse como NOT NULL. SQL Server establece
automáticamente la restricción NOT NULL para todas
las columnas de llave primaria si la restricción NOT
NULL no se especifica para estas columnas.

SQL Server también crea automáticamente un índice

agrupado único (o un índice no agrupado si se especifica
como tal) cuando crea una clave primaria.

Ejemplos de cómo utilizar la PRIMARY KEY de SQL

Server
El siguiente ejemplo crea una tabla con una llave primaria
que consta de una columna:
CREATE TABLE sales.actividades (
actividad_id INT PRIMARY KEY IDENTITY,
actividad_name VARCHAR (255) NOT NULL,
actividad_date DATE NOT NULL
);

En esta tabla sales.actividades, la columna actividad_id

es la columna de llave principal. Significa que la
columna activity_id contiene valores únicos.
La propiedad IDENTITY se usa para que la
columna actividad_id genere automáticamente
valores enteros únicos.
La siguiente instrucción crea una nueva tabla denominada
sales.participantes cuya llave principal consta de dos
columnas:
CREATE TABLE sales. participantes(
actividad_id int,
cliente_id int,
PRIMARY KEY(actividad_id, cliente_id)
);

En este ejemplo, los valores en la columna actividad_id o

cliente_id pueden estar duplicados, pero cada
combinación de valores de ambas columnas debe ser
única.

Normalmente, una tabla siempre tiene una llave primaria

definida en el momento de la creación. Sin embargo, a
veces, una tabla existente puede no tener una llave
primaria definida. En este caso, puede agregar una llave
primaria a la tabla utilizando la instrucción ALTER
TABLE. Considere el siguiente ejemplo:
La siguiente instrucción crea una tabla sin una llave
primaria:
CREATE TABLE sales.eventos(
evento_id INT NOT NULL,
evento_name VARCHAR(255),
start_date DATE NOT NULL,
duration DEC(5,2)
);
Para hacer que la columna evento_id sea la llave principal,
use la siguiente instrucción ALTER TABLE:
ALTER TABLE sales.eventos
ADD PRIMARY KEY(evento_id);
Ten en cuenta que si la tabla sales.eventos ya tiene datos,
antes de modificar la columna evento_id como clave
principal, debes asegurarte que los valores en evento_id
sean únicos.

¿Cómo utilizar las restricciones de llave foránea o

FOREIGN KEY de SQL Server?
La llave foránea o FOREIGN KEY, es una columna o varias
columnas, que sirven para señalar cual es la llave
primaria de otra tabla.
La columna o columnas señaladas como FOREIGN KEY, solo
podrán tener valores que ya existan en la llave
primaria PRIMARY KEY de la otra tabla.

La integridad referencial asegura que se mantengan las

referencias entre las claves primarias y las externas.
También controla que no pueda eliminarse un registro de
una tabla ni modificar la llave primaria si una llave foránea
o externa hace referencia al registro.

Introducción a la restricción de llave foránea, externa

o FOREIGN KEY de SQL Server

Considere las siguientes tablas grupo_vendedores y de

vendedores:
CREATE TABLE procurement.grupo_vendedores (
grupo_id INT IDENTITY PRIMARY KEY,
grupo_name VARCHAR (100) NOT NULL
);

CREATE TABLE procurement.vendedores(

vendedor_id INT IDENTITY PRIMARY KEY,
vendedor_name VARCHAR(100) NOT NULL,
grupo_id INT NOT NULL,
);
Cada vendedor pertenece a un grupo de vendedores y cada
grupo de vendedores puede tener cero o más vendedores.
La relación entre las tablas grupo_vendedores y vendedores
es de uno a muchos.
Para cada fila en la tabla de vendedores, siempre puede
encontrar una fila correspondiente en la tabla
grupo_vendedores.

Sin embargo, con la configuración de tablas, puede insertar

una fila en la tabla de vendedores sin una fila
correspondiente en la tabla grupo_vendedores. También
puede eliminar una fila en la tabla grupo_vendedores sin
actualizar o eliminar las filas correspondientes en la tabla de
vendedores que da como resultado da filas huérfanas en la
tabla de vendedores.

Para imponer el enlace entre los datos en las tablas

grupo_vendedores y vendedores, debe establecer una llave
foránea, externa o FOREIGN KEY en la tabla de vendedores.

Una llave foránea, externa o FOREIGN KEY es una columna o

un grupo de columnas en una tabla que identifica de forma
exclusiva una fila de otra tabla (o la misma tabla en caso de
autorreferencia).
Para crear una llave foránea, usa la restricción FOREIGN
KEY.

Las siguientes declaraciones eliminan la tabla de

vendedores y la recrean con una restricción FOREIGN KEY:
DROP TABLE vendedores;
CREATE TABLE procurement.vendedores (
vendedor_id INT IDENTITY PRIMARY KEY,
vendedor_name VARCHAR(100) NOT NULL,
grupo_id INT NOT NULL,
CONSTRAINT fk_grupo FOREIGN KEY
(grupo_id)
REFERENCES procurement.grupo_vendedores
(grupo_id)
);
La tabla grupo_vendedores es la tabla primaria a la cual se
hace referencia en la restricción de llave foránea o FOREIGN
KEY. La tabla de vendedores se denomina tabla secundaria,
que es la tabla a la que se aplica la restricción de llave
foránea o FOREIGN KEY.

En la declaración anterior, la siguiente cláusula crea una

restricción FOREIGN KEY denominada fk_grupo que vincula
el grupo_id en la tabla de vendedores al grupo_id en la tabla
de grupo_vendedores:
CONSTRAINT fk_grupo FOREIGN KEY (grupo_id)
REFERENCES procurement.grupo_vendedores
(grupo_id)
Sintaxis de restricción FOREIGN KEY de SQL Server
La sintaxis general para crear una restricción FOREIGN
KEY es la siguiente:

CONSTRAINT fk_constraint_name
FOREIGN KEY (column_1, column2,...)
REFERENCES
parent_table_name(column1,column2,..)
Examinemos esta sintaxis a detalle.

• Primero, especifique el nombre de

restricción FOREIGN KEY después de la palabra
llave CONSTRAINT. El nombre de la restricción es
opcional, por lo tanto, es posible definir una
restricción FOREIGN KEY de la siguiente manera:
• FOREIGN KEY (column_1, column2,...)
REFERENCES
parent_table_name(column1,column2,..)

En este caso, SQL Server generará

automáticamente un nombre para la
restricción FOREIGN KEY.

• En segundo lugar, especifique una lista de

columnas de llave foránea separadas por comas
encerradas entre paréntesis después de la palabra
clave FOREIGN KEY.
• En tercer lugar, especifique el nombre de la tabla
primaria a la que hace referencia la llave foránea y
una lista de columnas separadas por comas que
tiene un enlace con la columna en la tabla
secundaria.
Ejemplo de restricción FOREIGN KEY de SQL Server

• Primero, inserte algunas filas en la tabla

grupo_vendedores:
• INSERT INTO
procurement.grupo_vendedores(grupo_name)
• VALUES('Estrada Web Group Apps'),
• ('Estrada Web Group SQL Server),
('Estrada Web Group ASPNET');

• En segundo lugar, inserte un nuevo vendedor con

un grupo de vendedores en la tabla de vendedores:
• INSERT INTO procurement.vendedores (vendedor_name, grupo_id)
VALUES('Estrada Web Grouo Corp',1);

La declaración funcionó como se esperaba.

• Tercero, intente insertar un nuevo vendedor cuyo

grupo de vendedores no exista en la tabla
grupo_vendedores
INSERT INTO
procurement.vendedores(vendedor_name, grupo_id)
VALUES('EWG Corp',4);
SQL Server regresa el siguiente error:
The INSERT statement conflicted with the FOREIGN
KEY constraint "fk_grupo". The conflict occurred
in database "BikeStores", table
"procurement.grupo_vendedores ", column
grupo_id'.
En este ejemplo, debido a la restricción FOREIGN KEY, SQL
Server rechazó la inserción y emitió un error.

Más información de referencia

La restricción de llave foránea FOREIGN KEY garantiza la

integridad referencial. Significa que solo puede insertar
una fila en la tabla secundaria si hay una fila
correspondiente en la tabla primaria.

Además, la restricción de llave foránea FOREIGN KEY le

permite definir las acciones referenciales cuando la fila
de la tabla primaria se actualiza o elimina de la siguiente
manera:
FOREIGN KEY (foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE action
ON DELETE action;
ON UPDATE y ON DELETE especifican qué acción se
ejecutará cuando una fila de la tabla primaria se
actualice o se elimine. Las siguientes son acciones
permitidas: NO ACTION, CASCADE, SET NULL y SET DEFAULT