Como usar constraints en SQL Server

Usando restricciones en tablas en SQL Server

Al diseñar una base de datos, una de las mejores formas de trabajar es diseñar correctamente las tablas, los datos ingresados deben casi siempre de cumplir ciertas reglas, las cuales se pueden conseguir usando las restricciones para los campos de cada tabla.

Tipos de restricciones o constraints en SQL Server

Las restricciones o constraints en una tabla son las siguientes:

  1. Primary key: hace referencia al campo o campos que forman la clave primaria de la tabla, al especificar la clave primaria de una tabla se crea automáticamente el índice agrupado (Ver Índices). Se recomienda que todas las tablas de la base de datos tengan clave primaria. Sólo una clave primaria se puede crear en una tabla, esta restricción sirve para forzar la integridad de datos.
  2. Foreign key: clave foránea, esta restricción permite especificar la integridad de datos entre las tablas de la base de datos, la clave foránea hace referencia al campo que es la clave primaria en una tabla y la relaciona con un campo del mismo tipo de dato y de preferencia con el mismo nombre donde se especifica la clave foránea. También se puede especificar una acción al eliminar o actualizar el registro que es la clave foránea, no se recomienda el uso de esta opción.
  3. Check: la restricción de tipo Check permite especificar una o mas condiciones que debe de cumplir los datos para ser permitidos en el campo. Se puede definir con esta restricción los valores permitidos en un campo, por ejemplo, definir un campo Sexo donde los valores posibles son: M para masculino y F para femenino, el campo debería ser un campo nchar(1).
  4. Unique: la restricción de tipo unique permite restringir los datos de una columna que no es la clave primaria (Primary key) a valores que no se repitan. Por ejemplo, una tabla con Productos, cuya clave primaria es Codigo, no debería permitir productos con Descripción iguales, para conseguir esto, el campo Descripción debería tener una
    restricción de tipo Unique.
  5. Default: permite especificar un valor por defecto cuando el usuario no ingresa ningún dato en el campo, al ingresar los datos en una campo, estos pueden ser especificados como obligatorios, el campo o los campos de la clave primaria y la clave foránea son por su naturaleza obligatorios, los datos de los otros campos pueden especificarse como obligatorios incluyendo en la definición del campo «Not null», los campos que no son obligatorios se recomiendan especificar una restricción de tipo Default que permite ingresar un dato por defecto, cuando el usuario no inserta ningún valor. Se recomienda evitar los valores «Null» en las tablas. Por ejemplo, si
    no se tiene el precio de un producto insertado, se puede especificar como CERO, (obviamente el valor 0) y no dejar que se incluya Null en el campo.

Notas importantes

a. Las restricciones se pueden especificar al crear la tabla (Ver Crear tablas)
o al modificar la misma (Ver Alter Table).
b. Las restricciones de tipo Check y Foreign key se pueden deshabilitar.
c. Las restricciones de tipo Unique crear un índice no agrupado para el campo.
d. Se recomienda no realizar acción al eliminar o actualizar un registro que tiene clave foránea especificada.
e. Al agregar restricciones de tipo Foreign key o Check se verifican los datos
existentes en la tabla, se puede especificar la cláusula with Nocheck para que los datos actuales no sean verificados que cumplan con la restricción, los datos nuevos y los actualizados si deben de cumplir la restricción.
f. Se recomienda crear un índice en la columna que es clave foránea para aumentar el rendimiento en las consultas de varias tablas. (Ver Joins)

Incluir restriciones al crear la tablas en SQL Server

En las siguiente líneas se crearán tablas usando restricciones para cada una.
Creando la base de datos
create database BDRestricciones
go
Use BDRestricciones
go
Creando la tabla Categorias
Create table Categorias
(
CategoriasCodigo nchar(4),
CategoriasDescripcion nvarchar(50) not null,
CategoriasEstado nchar(1)
constraint CategoriasEstadoDF Default ‘A’,
constraint CategoriasPK primary key (CategoriasCodigo),
constraint CategoriasDescripcionUQ Unique (CategoriasDescripcion),
constraint CategoriasEstadoCK
check (CategoriasEstado = ‘A’ or CategoriasEstado = ‘E’)
)
go

En la tabla Categorias se han especificado las restricciones Primary key con el campo CategoriasCodigo, la restricción de tipo Unique para el campo CategoriasDescripcion que crea un índice no agrupado único para el campo y para el campo CategoriasEstado se han incluído las restricciones Default para el valor por defecto ‘A’ que significa ‘Activa’, que cambia a ‘E’ cuando se elimina, la restricción de tipo Check para el campo CategoriasEstado restringe los valores posibles para el campo a solamente las letras A y E.
La restricción primary key especificada crea el indice agrupado para la tabla llamado CategoriasPK.

Podemos ver la estructura de la tabla con la instrucción siguiente:
sp_help Categorias
go

Tabla Productos
Create table Productos
(
ProductosCodigo nchar(10),
ProductosDescripcion nvarchar(100) not null,
ProductosPrecio Numeric(9,2)
constraint ProductosPrecioDF default 0,
ProductosStock Numeric(9,2)
constraint ProductosStockDF default 0,
CategoriasCodigo nchar(4),
ProductosFechaRegistro Date constraint ProductosFechaRegistroDF Default GetDate(),
ProductosEstado nchar(1),
constraint ProductosPK Primary key (ProductosCodigo),
constraint ProductosCategoriasFK Foreign key (CategoriasCodigo)
references Categorias(CategoriasCodigo),
constraint ProductosDescripcionUQ Unique (ProductosDescripcion),
constraint ProductosEstadoCK
check (ProductosEstado = ‘A’ or ProductosEstado = ‘E’),
constraint ProductosPrecioCK check (ProductosPrecio >=0),
constraint ProductosStockCK check (ProductosStock >=0),
)
go

Las restricciones de la tabla las podemos visualizar con la siguiente instrucción
sp_help Productos
go

Como agregar restricciones en una tabla creada en SQL Server

Para insertar restricciones en una tabla creada se utiliza Alter Table.
Creando una tabla Clientes
Create table Clientes
(
ClientesCodigo nchar(15),
ClientesRazonSocial nvarchar(100),
ClientesDireccion nvarchar(200),
ClientesEstado nchar(1),
ClientesFechaRegistro Date
)
go

Restricción para la clave primaria
Alter table Clientes alter column ClientesCodigo nchar(15) not null
go
Alter table Clientes add constraint ClientesPK Primary key (ClientesCodigo)
go
Restricción Unique para la Razón Social
Alter table Clientes add constraint ClientesRazonSocialUQ Unique (ClientesRazonSocial)
go
Restricción Default para el estado con valor A
Alter table Clientes add constraint ClientesEstadoDF Default ‘A’ for ClientesEstado
go
Restricción Check para el estado permitiendo valores A y E
Alter table Clientes add constraint ClientesEstadoCK
Check (ClientesEstado = ‘A’ or ClientesEstado = ‘E’)
go
Restricción Default para la fecha de registro
Alter table Clientes add constraint ClientesFechaRegistroDF Default GetDate()
for ClientesFechaRegistro
go
Restricción Check para la fecha de registro que no permita valores después de la fecha actual
Alter table Clientes add constraint ClientesFechaRegistroCK
Check (ClientesFechaRegistro < GetDate()) go

Para visualizar las restricciones en la tabla usamos
sp_help Clientes
go

Tabla Facturas
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)
)
go

Agregar la restricción de tipo Foreign Key para relacionar la tabla de Facturas con Clientes
Alter table Facturas Add constraint FacturasClienteFK Foreign key (ClientesCodigo)
references Clientes (ClientesCodigo)
go

Como agregar restricciones en SQL Server

Agregar restricciones de tipo Check cuando existen datos que no cumplen con las condiciones, para este ejemplo se va a crear una tabla para empleados, se va a incluir un campo sueldo y se insertarán valores, dos de los cuales son menores a 2500. Luego se agregará la restricción de tipo Check que compruebe que los sueldos debe ser mayores o iguales a 2500, como existen algunos que no cumplen se debe usar la cláusula with Nocheck.

Tabla Empleados, se incluye el campo Sueldo
Create table Empleados
(
EmpleadosCodigo nchar(5),
EmpleadosPaterno nvarchar(50),
EmpleadosMaterno nvarchar(50),
EmpleadosNombres nvarchar(50),
EmpleadosFechaNacimiento Date,
EmpleadosSueldo Numeric(9,2)
constraint EmpleadosPK primary key (EmpleadosCodigo)
)
go
Agregar registros, note que algunos de los ingresados tienen un sueldo menor a 2500
insert into Empleados values
(‘AR996′,’Chavez’,’Pereda’,’Carlos’,’15/09/2000′,1800),
(‘TR467′,’Terranova’,’Wong’,’José’,’24/02/1996′,3800),
(‘BN789′,’Martinez’,’Alva’,’Cecilia’,’20/10/1983′,3850),
(‘VT678′,’Sánchez’,’Llanos’,’Aracely’,’15/09/2000′,2980),
(‘BH789′,’Nicolini’,’Mendoza’,’Amalia’,’23/07/1970′,1500)
go
Restricción de tipo Check para que el sueldo sea mayor o igual a 2500
Alter table Empleados add constraint EmpleadosSueldoCK Check (EmpleadosSueldo >= 2500)
go
Mensaje
Msg 547, Level 16, State 0, Line 185
Instrucción ALTER TABLE en conflicto con la restricción CHECK ‘EmpleadosSueldoCK’. El conflicto ha aparecido en la base de datos ‘BDRestricciones’, tabla ‘dbo.Empleados’, column ‘EmpleadosSueldo’.

No es posible agregar la restricción porque hay registros que no cumplen con la condición, por lo tanto se debe usar With Nocheck.
Alter table Empleados With Nocheck
add constraint EmpleadosSueldoCK Check (EmpleadosSueldo >= 2500)
go
Para visualizar los valores de los sueldos que no cumplen. (Ver Comandos DBCC )
DBCC Checkconstraints(Empleados)
go
La imagen muestra los valores que no cumplen, puede notar que son los valores de 1500 y 1800

Al listar los registros
Select * from Empleados where EmpleadosSueldo = 1500 or EmpleadosSueldo = 1800
go

Visualizar las restricciones de la base de datos

Ver las restricciones de tipo Check
Select * from sys.check_constraints
go
Ver las restricciones de tipo Foreign Key
Select * from sys.foreign_keys
go
Ver las restricciones de tipo Unique
Select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = ‘unique’
go
Ver las restricciones de tipo Primary key
Select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = ‘PRIMARY KEY’
go
Ver las restricciones de tipo Default
select * from sys.default_constraints
go

Como activar y desactivar las restricciones en SQL Server

Activar y desactivar contraints Check y Foreign key
Se pueden activar o desactivar las restricciones de tipo Check y Foreign Key
para esto debemos usar la claúsula Nocheck para desactivar y Check para activar en la instrucción Alter Table.
Desactivar Check para los sueldos
Alter table Empleados Nocheck constraint EmpleadosSueldoCK
go
Probar ingresando un registro que no cumpla con ser el sueldo mayor o igual a 2500
insert into Empleados values
(‘BY555′,’Palomino’,’Angeles’,’Susana’,’31/07/1977′,1780)
go
Si se pudo insertar.
Activar la restricción
Alter table Empleados check constraint EmpleadosSueldoCK
go
Select * from Empleados
go

Eliminar las restricciones
Para eliminar las restricciones se utiliza la clásula Drop constraint de la instrucción Alter table.
Eliminar la restricción Default para la fecha de registro de la tabla clientes
Alter table Clientes drop constraint ClientesFechaRegistroDF
go