Foreign key en SQL Server

Foreign key en SQL Server

Relacionando tablas en SQL Server, para relacionar dos tablas en una base de datos en SQL Server se debe usar la restricción de tipo Foreign Key que permite establecer las reglas de negocio entre dos entidades, lo que se convierte en la relación entre dos tablas de la base de datos.

En este artículo se muestran ejemplos de como relacionar tablas haciendo uso de la restricción de tipo Foreign Key. Este tipo de relación va a asegurar la integridad referencial entre las tablas de la base de datos.

Para mayor información ver
Restricciones en SQL Server
Create tablas
Modificar tablas
Insertar registros
Actualizar registros

Crear y abrir una base de datos.

Create database Restricciones
go
use Restricciones
go

Ejercicio 1

Relación Uno – Muchos
Creando las tablas y especificando las restricciones al crearlas.
Tablas Categorias y Productos, un producto pertenece a una categoría.

Create table Categorias
(
CategoriasCodigo nchar(5),
CategoriasDescripcion nvarchar(50) not null,
CategoriasEstado nchar(1) constraint CategoriasEstadoDF default ‘A’,
CategoriasFechaCreacion Date constraint CategoriasFechaCreacionDF default GetDate(),
constraint CategoriasPK primary key (CategoriasCodigo),
constraint CategoriasEstadoCK check (CategoriasEstado = ‘A’ or CategoriasEstado = ‘E’),
constraint CategoriasFechaCreacionCK check (CategoriasFechaCreacion > GetDate())
)
go
Note que la tabla tiene la restricción Primary key con el campo Codigo, el estado se ha especificado con dos posibles valores A de Activo y E de Eliminado, siendo la restricción CategoriasEstadoDF la que especifica que el valor por defecto es A. Para la fecha de creación se ha especificado la restricción CategoriasFechaCreacionCK que impide que la fecha sea después de la fecha actual.

Create table Productos
(
ProductosCodigo nchar(8),
ProductosDescripcion nvarchar(50) not null,
CategoriasCodigo nchar(5),
ProductosPrecio Numeric(9,2) constraint ProductosPrecioDF default 0,
ProductosStock Numeric(9,2) constraint ProductosStockDF default 0,
ProductosEstado nchar(1) constraint ProductosEstadoDF default ‘A’,
constraint ProductosPK primary key (ProductosCodigo),
constraint ProductosEstadoCK check (ProductosEstado = ‘A’ or ProductosEstado = ‘E’),
constraint ProductosPrecioCK check (ProductosPrecio >=0),
constraint ProductosStockCK check (ProductosStock >=0),
constraint ProductosCategoriasFK Foreign key (CategoriasCodigo)
references Categorias(CategoriasCodigo)
)
go
El diagrama entre las dos tabla se muestra en la siguiente imagen.

Ejercicio 2

Relación Uno – Muchos con varios atributos

Clientes, Facturas, Detalle de Facturas y Productos
Una factura es generada por un empleado, no se va a crear aún la tabla de empleados para luego ver como se agregan campos y restricciones Foreign Key en tablas ya creadas.

Create table Clientes
(
ClientesCodigo nchar(15),
ClientesNombre nvarchar(100) not null,
ClientesDireccion nvarchar(200),
constraint ClientesPK Primary key (ClientesCodigo)
)
go
Create table Facturas
(
FacturasNumeroSerie nchar(5),
FacturasNumeroFactura nchar(7),
FacturasFecha DateTime,
FacturasMontoSinIGV Numeric(9,2),
FacturasPorcentajeDeIGV Numeric(8,5),
ClientesCodigo nchar(15),
FacturasMontoIGV As (FacturasMontoSinIGV * FacturasPorcentajeDeIGV),
FacturasMontoTotal As (FacturasMontoSinIGV + FacturasMontoSinIGV * FacturasPorcentajeDeIGV )
constraint FacturasPK primary key (FacturasNumeroSerie, FacturasNumeroFactura),
constraint FacturasClienteFK Foreign key (ClientesCodigo)
references Clientes(ClientesCodigo)
)
go

Tabla Detalle de Factura
Create table DetalleFactura
(
FacturasNumeroSerie nchar(5),
FacturasNumeroFactura nchar(7),
ProductosCodigo nchar(8),
ProductosDescripcion nvarchar(100),
DetalleCantidadVendida Numeric(9,2),
DetallePrecioVenta Numeric(9,2),
DetalleOImporte As (DetalleCantidadVendida * DetallePrecioVenta)
constraint DetalleFacturaPK Primary key
(FacturasNumeroSerie,FacturasNumeroFactura,ProductosCodigo),
constraint DetalleFacturaFacturasFK
Foreign key (FacturasNumeroSerie,FacturasNumeroFactura)
references Facturas (FacturasNumeroSerie,FacturasNumeroFactura),
constraint DetalleFacturaProductosFK Foreign key (ProductosCodigo)
references Productos (ProductosCodigo)
)
go
El diagrama para el ejercicio 2 (incluye Categorías) se muestra en la siguiente imagen.

Ejercicio 3

Relación recursiva

Una relación recursiva se crea cuando la tabla se relaciona consigo misma. Tabla Empleados, un empleado es el jefe de otro y está en la misma tabla, en este caso se va a crear una relación recursiva entre el empleado jefe y sus subordinados.

Create table Empleados
(
EmpleadosCodigo nchar(4),
EmpleadosPaterno nvarchar(50) not null,
EmpleadosMaterno nvarchar(50) not null,
EmpleadosNombres nvarchar(50) not null,
EmpleadosFechaNacimiento Date,
EmpleadosSexo nchar(1),
EmpleadosCodigoJefe nchar(4),
constraint EmpleadosPK primary key (EmpleadosCodigo),
constraint EmpleadosEmpleadosFK foreign key (EmpleadosCodigoJefe)
references Empleados(EmpleadosCodigo),

constraint EmpleadosSexoCK check (EmpleadosSexo = ‘F’ or EmpleadosSexo = ‘M’)
)
go
El diagrama para el ejercicio 3 se muestra en la siguiente imagen. Note que se han incluido dos campos EmpleadosCodigo y EmpleadosCodigoJefe para crear la relación recursiva.

Ejercicio 4

Agregar restricción FK a una tabla existente.
Relacionar la tabla Facturas del ejercicio 2 con Empleados, un empleado registra una factura.
Pasos:
Primero: Agregar el campo en la tabla Facturas, es recomendable que el campo sea tenga mismo nombre de la tabla de Empleados, lo que sí debe ser obligatorio es que el tipo de campo sea el mismo.
Segundo: Agregar la restricción FK entre las dos tablas.

Alter table Facturas Add EmpleadosCodigo nchar(4) not null
go
Alter table Facturas Add constraint FacturasEmpleadosFK
Foreign key (EmpleadosCodigo)
references Empleados (EmpleadosCodigo)
go
El diagrama para el ejercicio 4 se muestra en la siguiente imagen.

Ejercicio 5

Agregar una restricción FK entre tablas con registros, considerando que algunos de los registros existentes no cumplen con la restricción. Lo que es necesario es agregar la restricción sin que se compruebe la integridad referencial de los registros existentes.
Para este ejercicio se van a agregar registros en la tabla Empleados, luego se va a crear la tabla Departamentos y luego establecer la relación, hay que resaltar que al establecer la relación en la tabla Empleados no se va a especificar el código del departamento que es la clave foránea.

Registros en Empleados sin Departamento

set dateformat dmy
insert into Empleados values
(‘0001′,’Luque’,’Sánchez’,’Fernando’,’23/07/1966′,’M’,Null),
(‘0002′,’Mendoza’,’Cevallos’,’Carlos’,’14/07/1986′,’M’,’0001′),
(‘0003′,’Sandoval’,’Terranova’,’Raquel’,’08/03/1990′,’F’,’0001′),
(‘0004′,’Villacorta’,’Pérez’,’Antonio’,’13/06/1974′,’M’,’0003′),
(‘0005′,’Pereda’,’Chávez’,’Ingrid’,’10/02/1999′,’F’,’0003′),
(‘0006′,’Zapata’,’Sánchez’,’Luis’,’19/09/1970′,’M’,’0003′)
go

Crear y agregar registros a la tabla Departamentos
Create table Departamentos
(
DepartamentosCodigo nchar(5),
DepartamentosDescripcion nvarchar(50) not null,
DepartamentosEstado nchar(1) constraint DepartamentosEstadoDF default ‘A’,
constraint DepartamentosPK primary key (DepartamentosCodigo),
constraint DepartamentosEstadoCK check (DepartamentosEstado = ‘A’ or DepartamentosEstado = ‘E’)
)
go
Registros en Departamentos
insert into Departamentos values
(‘D0101′,’Gerencia General’,’A’),
(‘D0102′,’Administración’,’A’),
(‘D0201′,’Producción’,’A’),
(‘D0202′,’Ventas’,’A’),
(‘D0203′,’Planificación y control’,’A’),
(‘D0301′,’Finanzas y Presupuesto’,’A’)
go
Agregar la relación (Foreign key) entre las tablas Empleados y Departamentos
Alter table Empleados add DepartamentosCodigo nchar(5)
go
Alter table Empleados add constraint
EmpleadosDepartamentosFK foreign key (DepartamentosCodigo)
references Departamentos (DepartamentosCodigo)
go


El listado de los empleados
select * from Empleados
go
El resultado se muestra en la siguiente imagen.
Note que en el código del departamento todos los empleados tienen el valor Null. Es necesario corregir uno por uno.

Insertar un empleado sin departamento
insert into Empleados
([EmpleadosCodigo], [EmpleadosPaterno], [EmpleadosMaterno],
[EmpleadosNombres], [EmpleadosFechaNacimiento],
[EmpleadosSexo], [EmpleadosCodigoJefe])
values
(‘0007′,’Rojas’,’Alvarado’,’Mónica’,’06/02/1997′,’F’,’0001′)
go
Insertar un empleado con un código de Departamento que no existe.
insert into Empleados
([EmpleadosCodigo], [EmpleadosPaterno], [EmpleadosMaterno],
[EmpleadosNombres], [EmpleadosFechaNacimiento],
[EmpleadosSexo], [EmpleadosCodigoJefe], [DepartamentosCodigo])
values
(‘0008′,’Casanova’,’Llanos’,’Julio’,’11/04/1988′,’M’,’0001′,’D0010′)
go
Mensaje:

Msg 547, Level 16, State 0, Line 209
Instrucción INSERT en conflicto con la restricción FOREIGN KEY ‘EmpleadosDepartamentosFK’. El conflicto ha aparecido en la base de datos ‘Restricciones’, tabla ‘dbo.Departamentos’, column ‘DepartamentosCodigo’.
Se terminó la instrucción.

Insertar un empleado con un código de Departamento que SI existe.
insert into Empleados
([EmpleadosCodigo], [EmpleadosPaterno], [EmpleadosMaterno],
[EmpleadosNombres], [EmpleadosFechaNacimiento],
[EmpleadosSexo], [EmpleadosCodigoJefe], [DepartamentosCodigo])
values
(‘0008′,’Casanova’,’Llanos’,’Julio’,’11/04/1988′,’M’,’0001′,’D0102′)
go
El listado de los empleados
select * from Empleados
go
El resultado se muestra en la siguiente imagen.

Actualizar el registro con código 0001 asignando el departamento de Gerencia General con código D0101
Update Empleados Set DepartamentosCodigo = ‘D0101’
where EmpleadosCodigo = ‘0001’
go

Ejercicio 6

Relación Muchos – Muchos

En una relación muchos – muchos se deben crear tres tablas, en una de estas se establecen las claves foráneas de las otras dos adicionando los atributos propios de la relación.
Para el tipo de relación muchos a muchos se va a crear una tabla de Proyectos, donde varios empleados pueden ser asignados a un proyecto y varios proyectos pueden ser asignados a un empleado. Se creará la tabla de Proyectos y EmpleadosProyectos

Create table Proyectos
(
ProyectosCodigo nchar(6),
ProyectosDescripcion nvarchar(50) not null,
ProyectosEstado nchar(1) constraint ProyectosEstadoDF default ‘A’,
ProyectosFechaInicio Date,
ProyectosDuracionMeses Numeric(9,2),
constraint ProyectosPK primary key (ProyectosCodigo),
constraint ProyectosEstadoCK check (ProyectosEstado = ‘A’ or ProyectosEstado = ‘E’)
)
go

La tabla empleados ya está creada en el ejercicio 3.

La tabla de Proyectos Empleados
Create table ProyectosEmpleados
(
EmpleadosCodigo nchar(4),
ProyectosCodigo nchar(6),
ProyectosEmpleadosEstado nchar(1)
constraint ProyectosEmpleadosEstadoDF default ‘A’,
ProyectosEmpleadosRol nvarchar(20),
ProyectosEmpleadosMetodologia nvarchar(30),
constraint ProyectosEmpleadosPK primary key (EmpleadosCodigo, ProyectosCodigo),
constraint ProyectosEmpleadosEstadoCK check
(ProyectosEmpleadosEstado = ‘A’ or ProyectosEmpleadosEstado = ‘E’),
constraint ProyectosEmpleadosEmpleadosFK Foreign key (EmpleadosCodigo)
references Empleados(EmpleadosCodigo),
constraint ProyectosEmpleadosProyectosFK Foreign key (ProyectosCodigo)
references Proyectos(ProyectosCodigo)
)
go
El diagrama para la relación muchos a muchos se muestra en la siguiente imagen.

Desactivar una restricción Foreign key

No se recomienda desactivar las restricciones de tipo Foreign key, pero la forma de desactivar es usando la cláusula Nocheck en la instrucción Alter Table.
Desactivar la FK EmpleadosDepartamentosFK
Alter table Empleados nocheck constraint EmpleadosDepartamentosFK
go
El inconveniente es que se pueden insertar o editar registros con códigos de departamentos que no existen.
Se va a insertar un empleado con el departamento con código D1234, que no existe en la tabla Departamentos.
insert into Empleados
([EmpleadosCodigo], [EmpleadosPaterno], [EmpleadosMaterno],
[EmpleadosNombres], [EmpleadosFechaNacimiento],
[EmpleadosSexo], [EmpleadosCodigoJefe], [DepartamentosCodigo])
values
(‘0009′,’Campos’,’Segura’,’Carmela’,’01/05/1977′,’F’,’0002′,’D1234′)
go
El listado de los empleados
select * from Empleados
go
El resultado se muestra en la siguiente imagen.

Activar una restricción Foreign key

Para activar la restricción de tipo FK se usa la cláusula Check Constraint de la instrucción Alter Table.
Alter table Empleados check constraint EmpleadosDepartamentosFK
go

Eliminar una restricción Foreign key

No se recomienda eliminar las restricciones FK, para eliminar se utiliza la cláusula Drop constraint de la instrucción Alter table.
Alter table Empleados drop constraint EmpleadosDepartamentosFK
go