subconsultas - subquery sql server ejemplos

Sub consultas en la restricción de verificación

Question

Tengo una tabla diseñada en SQL-Server 2008 R2.

Tengo una columna en esa tabla que debe verificarse en otra tabla cuando los datos se están insertando.

ALTER TABLE Table1
WITH CHECK ADD CONSTRAINT CK_Code
CHECK (MyField in (Select Field From Table2))

Esto causa un error

Las subconsultas no están permitidas en este contexto. Solo se permiten expresiones escalares.

He visto esta pregunta sobre Restricción de verificación: las subconsultas no están permitidas en este contexto .

¿Hay alguna forma de lograr esto sin usar un disparador?

Answer 1

En primer lugar, en su ejemplo, claramente necesita Restricción FK.

Otra posibilidad es usar view con WITH CHECK OPTION y dar acceso al usuario a través de él:

CREATE TABLE Table1(i INT PRIMARY KEY, CK_Code CHAR(1));
CREATE TABLE Table2(Field CHAR(1));
INSERT INTO Table2(Field) VALUES (''A''),(''B''), (''C'');
GO
CREATE VIEW v_Table1
AS
SELECT *
FROM Table1
WHERE CK_code IN (SELECT Field FROM Table2) -- here goes your subquery check
WITH CHECK OPTION;

Cuando intenta insertar datos que violan su "restricción" como:

INSERT INTO v_Table1(i, CK_Code)
VALUES(10, ''D'');

Conseguirás:

El intento de inserción o actualización falló porque la vista de destino especifica CON CHEQUE OPCIÓN o abarca una vista que especifica CON OPCIÓN DE VERIFICACIÓN y una o más filas resultantes de la operación no calificaron bajo la restricción CHEQUEAR OPCIÓN.

La instrucción se ha terminado.

LiveDemo

Answer 2

No puede tener subconsultas dentro de las restricciones de verificación. Lo que puede hacer es usar una UDF que devuelva un valor escalar dentro de la restricción de verificación.

Paso 1: crea la tabla

USE CTBX
GO
CREATE TABLE RawMaterialByGender
(
RMGID int primary key identity(1,1),
RMID smallint foreign key references RawMaterialMaster(RMID),
LeveLMasterID smallint foreign key references LevelMaster(LevelTextID),
IsDeleted bit
)

Paso 2: crea la UDF que devuelve un escalar

Create FUNCTION [dbo].[IsValidLevelMasterGender](@LevelMasterID smallint)
RETURNS bit
AS
BEGIN
DECLARE @count smallint;
DECLARE @return bit;
SELECT @count = count(LevelTextID)
FROM [LevelMaster]
WHERE LevelCategoryID = 3 AND IsActive = 1 AND LevelTextID=@LevelMasterID
IF(@count = 0)
SET @return = ''false'';
ELSE
SET @return = ''true'';
RETURN @return;
END;
GO

Paso 3: Altere la tabla para agregar la restricción CHECK

ALTER TABLE RawMaterialByGender
ADD CONSTRAINT check_LevelMasterID CHECK (dbo.IsValidLevelMasterGender(LeveLMasterID) = ''true'')

Answer 3

Tenga en cuenta que lo que realmente quiere es una restricción de clave externa. Dicho esto, para obtener una "consulta" en un cheque, puede escribir una función que contenga la consulta y muestre un valor escalar, luego use esa función en la restricción de verificación.

CREATE FUNCTION myFunction (
@field DATATYPE(?)
)
RETURNS VARCHAR(5)
AS
BEGIN
IF EXISTS (SELECT* FROM Table2 WHERE MYFIELD = @field)
return ''True''
return ''False''
END

Algo como eso. No probado.

Entonces puedes agregarlo a tu cheque como tal

ALTER TABLE Table1
WITH CHECK ADD CONSTRAINT CK_Code
CHECK (myFunction(MYFIELD) = ''True'')

Answer 4

ALTER TABLE Table1
ADD CONSTRAINT FK_Table1_Code FOREIGN KEY (MyField)
REFERENCES Table2 (Field) ;

Ref: http://msdn.microsoft.com/en-us/library/ms190273.aspx
Nota: No he verificado lo anterior para la sintaxis.