SCRIPT: CONSTRAINT EN SQL SERVER

--Las restricciones se almacenan en syscomments, sysreferences y sysconstraints. /* CREATE TABLE Tabla ( { < definiciónColumna > | < restricciónTabla > } [ ,...n ]) < definiciónColumna > ::= { columnatipoDeDatos } [ [ DEFAULT expresiónConstante ] [ < restricciónColumna > ] [ ,..n] < restricciónColumna > ::= [ CONSTRAINT nombreRestricción ] | [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] ] | [ [ FOREIGN KEY ] REFERENCES tablaRef [ ( columnaRef ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]] | CHECK ( expresiónLógica ) } < restricciónTabla > ::= [ CONSTRAINT nombreRestricción ] { [ { PRIMARY KEY | UNIQUE } [CLUSTERED | NONCLUSTERED] { ( columna [ ASC | DESC ] [ ,...n ] ) } ] | FOREIGN KEY [ ( columna [ ,...n ] ) ] REFERENCES tablaRef [ ( columnaRef [ ,...n ] ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] | CHECK ( condicionesBúsqueda ) } */ USE northwind CREATE TABLE dbo.Products ( ProductID int IDENTITY (1,1) NOT NULL, ProductName nvarchar (40) NOT NULL, SupplierID int NULL, CategoryID int NULL, QuantityPerUnit nvarchar (20) NULL, UnitPrice money NULL CONSTRAINT DF_Products_UnitPrice DEFAULT(0), UnitsInStock smallint NULL CONSTRAINT DF_Products_UnitsInStock DEFAULT(0), UnitsOnOrder smallint NULL CONSTRAINT DF_Products_UnitsOnOrder DEFAULT(0), ReorderLevel smallint NULL CONSTRAINT DF_Products_ReorderLevel DEFAULT(0), Discontinued bit NOT NULL CONSTRAINT DF_Products_Discontinued DEFAULT(0), CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductID), CONSTRAINT FK_Products_Categories FOREIGN KEY (CategoryID) REFERENCES dbo.Categories (CategoryID) ON UPDATE CASCADE, CONSTRAINT FK_Products_Suppliers FOREIGN KEY (SupplierID) REFERENCES dbo.Suppliers (SupplierID) ON DELETE CASCADE, CONSTRAINT CK_Products_UnitPrice CHECK (UnitPrice >= 0), CONSTRAINT CK_ReorderLevel CHECK (ReorderLevel >= 0), CONSTRAINT CK_UnitsInStock CHECK (UnitsInStock >= 0), CONSTRAINT CK_UnitsOnOrder CHECK (UnitsOnOrder >= 0) ) GO --devuelve todos los tipos de restricciones use northwind go sp_helpconstraint customers --informacion de esquema Select * from information_schema.table_constraints Select * from information_schema.check_constraints Select * from information_schema.referential_constraints go --CREACION DE CONSTRAINTS DROP TABLE INSTRUCTORES CREATE TABLE INSTRUCTORES (CODIGO INT IDENTITY(1,1) NOT NULL, NOMBRE VARCHAR(50), APELLIDO VARCHAR(50), TELEFONO VARCHAR(10) ) --LLAVE PRIMARIA ALTER TABLE INSTRUCTORES ADD CONSTRAINT PK_INSTRUCTORES PRIMARY KEY CLUSTERED (CODIGO) --VALOR PREDETERMINADO ALTER TABLE INSTRUCTORES ADD CONSTRAINT DF_NOMBRE DEFAULT 'SIN NOMBRE' FOR NOMBRE --AGREGAR UNA COLUMNA ALTER TABLE INSTRUCTORES ADD  NACIMIENTO SMALLDATETIME --RESTRICCION CHECK PARA NACIMIENTO ALTER TABLE INSTRUCTORES ADD CONSTRAINT CH_CUMPLEAÑOS CHECK (NACIMIENTO>='1995-01-01' AND NACIMIENTO<=GETDATE()) --VALORES UNICOS ALTER TABLE INSTRUCTORES ADD CONSTRAINT U_APELLIDO UNIQUE NONCLUSTERED (APELLIDO) --LLAVE FORANEA [CONSTRAINT nombreRestricción] [FOREIGN KEY] [(columna[,…n])] REFERENCES tablaRef [(columnaRef [,…n])]. [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] --BORRAR UN CONSTRAINT ALTER TABLE INSTRUCTORES DROP CONSTRAINT CH_CUMPLEAÑOS --NO COMPRUEBA LOS DATOS EXISTENTES UNA RESTRICCION SOLO SE PUEDE cHECK O FOREINGKEY USE Northwind ALTER TABLE dbo.Employees WITH NOCHECK------------------------------------------------->NO COMPRUEBA LOS DATOS EXISTENTES ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES dbo.Employees( USE Northwind ALTER TABLE dbo.Employees WITH NOCHECK ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES dbo.Employees(EmployeeID) --CARGAR NUEVOS DATOS SIN QUE SE VERIFIQUE LA RESTRICCION USE Northwind ALTER TABLE dbo.Employees NOCHECK CONSTRAINT FK_Employees_Employees USE Northwind ALTER TABLE dbo.Employees NOCHECK CONSTRAINT FK_Employees_Employees --CREAR UNA REGLA CREATE RULE AÑOS AS @NACIMIENTO>='1995-01-01' AND @NACIMIENTO<=GETDATE() --VINCULAR LA REGLA EXEC sp_bindrule 'AÑOS', 'INSTRUCTORES.NACIMIENTO' --DESVINCULAR LA REGLA EXEC sp_unbindrule   'INSTRUCTORES.NACIMIENTO' --BORRAR LA REGLA DROP RULE AÑOS --VALOR DEFAULT CREATE DEFAULT NADA AS 'unknown' --VINCULAR EL DEFAULT sp_bindefault NADA, 'INSTRUCTORES.NOMBRE' --DESVINCULAR sp_unbindefault 'INSTRUCTORES.NOMBRE' use northwind go sp_helpconstraint employees