你能将标量函数的输出与check语句中的标量进行比较吗?SQL

Question

创建表时，我想将标量函数的输出与CHECK语句中的标量值进行比较，但它似乎无法进行正确的比较。我希望支票对账单是"；"已检查"；如果标量函数返回1。有办法做到这一点吗？(函数在最后一个检查语句中调用(

该语句成功运行，如果我从CHECK函数中运行函数，它会返回正确的值(0或1(，但当我在函数返回值中插入满足1的数据时，会返回错误。这是我的代码，也是我尝试过的，

CREATE TABLE COLONO (
numero INT,
nome NVARCHAR (150) NOT NULL,
dtnascimento DATE NOT NULL CHECK(DATEDIFF(year, dtnascimento, GETDATE()) BETWEEN 6 AND 17),
contacto NVARCHAR(50) CHECK(contacto LIKE '+351[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
escolaridade INT NOT NULL CHECK(escolaridade BETWEEN 1 AND 12),
ccidadao VARCHAR(15),
cutente DECIMAL(10,0),
equipa INT NOT NULL FOREIGN KEY REFERENCES EQUIPA(numero),
PRIMARY KEY (numero),
UNIQUE (contacto),
UNIQUE (ccidadao),
UNIQUE (cutente), 
CHECK(dbo.get_grupo6(equipa,DATEDIFF(year, dtnascimento, GETDATE())) = 1)
);

我的函数get_grupo6是

CREATE FUNCTION get_grupo6(@equipa INT,@idade INT)
RETURNS INT
AS
BEGIN
RETURN(
SELECT CASE WHEN @idade BETWEEN idademinima AND idademaxima THEN 1 ELSE 0 END 
FROM grupo WHERE nome IN(SELECT grupo FROM equipa WHERE numero=@equipa)
)
END

grupo表的创建如下

CREATE TABLE GRUPO (
nome NVARCHAR(25) CHECK(nome IN ('iniciados','júniores','séniores')),
idademinima INT NOT NULL CHECK(idademinima>5), 
idademaxima INT NOT NULL CHECK(idademaxima<18), 
PRIMARY KEY (nome),
CHECK(idademaxima>idademinima)
);

设备表的创建遵循

CREATE TABLE EQUIPA (
numero INT,
grupo NVARCHAR(25) NOT NULL,
designacao NVARCHAR(150) NOT NULL,
PRIMARY KEY (numero),
FOREIGN KEY (grupo) REFERENCES GRUPO(nome),
);

当我插入数据时，即使函数返回了我期望它返回的值，也会返回以下错误(不应返回错误(：

消息547，级别16，状态0，第219行INSERT语句与CHECK约束"相冲突；CK__COLONO__110145FE"；。冲突发生在数据库"中；"master"；，表"；dbo。COLONO"；。语句已终止。消息547，级别16，状态0，第220行INSERT语句与CHECK约束"相冲突；CK__COLONO__110145FE"；。冲突发生在数据库"中；"master"；，表"；dbo。COLONO"；。

另外，这是我插入的

INSERT INTO GRUPO VALUES('iniciados',6,10)
INSERT INTO GRUPO VALUES('séniores',15,17)
INSERT INTO GRUPO VALUES('júniores',11,14)
INSERT INTO EQUIPA VALUES(20, 'iniciados', 'Um grupo espectacular', 8100)
INSERT INTO EQUIPA VALUES(21, 'iniciados', 'Um grupo fraquito', 8055)
INSERT INTO EQUIPA VALUES(22, 'júniores', 'Um grupo sem derrotas', 8080)
INSERT INTO EQUIPA VALUES(23, 'séniores', 'Um grupo de séniores', 8200)
INSERT INTO EQUIPA VALUES(100,'séniores','Um grupo dedicado',7001)
INSERT INTO EQUIPA VALUES(101,'júniores','Um grupo exemplar',7003)
INSERT INTO EQUIPA VALUES(102,'séniores','Um grupo inteligente',7004)
INSERT INTO EQUIPA VALUES(103,'séniores','Um grupo responsavel',7007)
INSERT INTO EQUIPA VALUES(209, 'iniciados', 'Um grupo sensato' , 8003)
INSERT INTO EQUIPA VALUES(210, 'júniores', 'O mais descarado' , 8004)
INSERT INTO EQUIPA VALUES(211, 'júniores', 'O melhor' , 8002)
INSERT INTO EQUIPA VALUES(212, 'séniores', 'Jan ao cubo' , 8009)
INSERT INTO COLONO VALUES(103,'João Amaral','2008-05-01','[phone]',6,'13845623',5053316251,13000,22)
INSERT INTO COLONO VALUES(998,'Luis Silva','2008-01-01','[phone]',6,'4529788',6116883976,14000,22)
INSERT INTO COLONO VALUES(663,'Ricardo Morgado','2007-08-22','[phone]',6,'84705373',2546154599,11040,22)
INSERT INTO COLONO VALUES(937,'Luis Moreira','2013-06-12','[phone]',6,'18062006',6079199549,55672,20)
INSERT INTO COLONO VALUES(837,'Carolina Esquivel','2013-03-03','[phone]',6,'10600058',6295896573,33332,21)
INSERT INTO COLONO VALUES(120,'Carolina Correia','2011-03-03','[phone]',6,'53421641',7604024824,44432,21)
INSERT INTO COLONO VALUES(744,'Carolina Esquivel','2009-01-29','[phone]',6,'49154963',7069630161,22123,22)
INSERT INTO COLONO VALUES(936,'Luisa Coutinho','2011-03-20','[phone]',6,'88893561',2981938455,44123,20)
INSERT INTO COLONO VALUES(764,'Isabel Paiva','2009-03-20','[phone]',6,'59615621',3874426665,11123,22)
INSERT INTO COLONO VALUES(285,'Maria Da Vincci','2005-03-20','[phone]',6,'53130407',9399759588,52695,23)
INSERT INTO COLONO VALUES(257,'Luis Dorei','2006-03-20','[phone]',6,'72302344',4865860563,43889,22)
INSERT INTO COLONO VALUES(988,'Noa Vicente','2007-03-20','[phone]',6,'44766751',7309911603,53333,22)
INSERT INTO COLONO VALUES(234,'Darwin James','2004-03-20','[phone]',6,'48034937',4137784431,85695,23)
INSERT INTO COLONO VALUES(667,'Cecilia Borges','2009-03-20','[phone]',6,'73250728',8286872702,35905,22)
INSERT INTO COLONO VALUES(368,'Matilde Borges','2013-03-20','[phone]',6,'73250721',8286872705,35905,22)
INSERT INTO COLONO VALUES(167,'Najeli Kingston','2013-03-20','[phone]',6,'73250722',8286872742,35906,22)
INSERT INTO COLONO VALUES(745,'Abdul Mallade','2013-03-20','[phone]',6,'73250724',8286872744,35907,22)
INSERT INTO COLONO VALUES(7008,'Barbara Guimar','2005-02-26','[phone]',10,'35697521',3498536745,17008,100)
INSERT INTO COLONO VALUES(7009,'Miguel Barbosa','2004-12-16','[phone]',11,'35697721',3498636644,17009,100)
INSERT INTO COLONO VALUES(7010,'Rafael Fernandes','2003-01-30','[phone]',12,'35557521',3498536473,17010,100)
INSERT INTO COLONO VALUES(7011,'Rubem Santos','2004-06-26','[phone]',11,'35287521',3498536742,17011,100)
INSERT INTO COLONO VALUES(7012,'Afonso Santos','2004-06-26','[phone]',11,'35707521',3498536741,17011,100)
INSERT INTO COLONO VALUES(7013,'Pedro Gomes','2005-09-13','[phone]',10,'35697211',3498536740,17012,100)
INSERT INTO COLONO VALUES(7014,'André Gomes','2005-09-13','[phone]',10,'35692228',3498536953,17012,100)
INSERT INTO COLONO VALUES(7015,'Daniel Fonseca','2003-08-28','[phone]',11,'35897521',3498565674,17013,100)
INSERT INTO COLONO VALUES(7016,'Maria Vermelho','2005-12-08','[phone]',9,'53697521',7498536715,17014,102)
INSERT INTO COLONO VALUES(7017,'Célia Norton','2003-10-31','[phone]',11,'63697721',3478636632,17015,102)
INSERT INTO COLONO VALUES(7018,'Catarina Pina','2003-11-08','[phone]',12,'37557521',3798536473,17016,102)
INSERT INTO COLONO VALUES(7019,'Carolina Milheiro','2004-05-25','[phone]',8,'74697521',7498536742,17017,102)
INSERT INTO COLONO VALUES(7020,'Margarida Borges','2004-07-22','[phone]',11,'95707521',9498536741,17018,102)
INSERT INTO COLONO VALUES(7021,'Raquel Gomes','2005-03-16','[phone]',10,'15697421',3494536740,17019,102)
INSERT INTO COLONO VALUES(7022,'Andreia Pires','2005-08-29','[phone]',9,'69697221',3498566953,17020,102)
INSERT INTO COLONO VALUES(7023,'João Amaro','2003-07-28','[phone]',12,'25897521',3498545674,17021,102)
INSERT INTO COLONO VALUES(008, 'Maria Silva','2009-07-12', '[phone]',7, '314154172',7894561239,00012,211)
INSERT INTO COLONO VALUES(007, 'Helena Luz','2008-07-10', '[phone]',8, '232657172',7894561237 ,00014 , 211)
INSERT INTO COLONO VALUES(009, 'Lisa Ye','2008-09-01', '[phone]',9, '289154172',7894561232 ,00015, 211)
INSERT INTO COLONO VALUES(010, 'Diana Fonseca', '2008-08-12', '[phone]',8, '276159672', 7894565837, 00016, 211)
INSERT INTO COLONO VALUES(011, 'Beatriz Coimbra','2007-07-11', '[phone]',7,'245169172', 7894577737,00017 , 211)
INSERT INTO COLONO VALUES(012, 'Beatriz Godinho', '2007-06-10', '[phone]',7, '333178972',7894563237 , 00018, 211)
INSERT INTO COLONO VALUES(013, 'André Churrasqueira','2005-11-08','[phone]', 9, '339727685',7894561337 , 00019, 209 )
INSERT INTO COLONO VALUES(014, 'Francisco Fonseca','2005-11-08', '[phone]',9 , '333369272',7894564437 ,00020 , 209 )
INSERT INTO COLONO VALUES(015, 'Jan Emanuel','2005-11-08', '[phone]',10 , '333345672',7894563937 ,00021 , 209 )
INSERT INTO COLONO VALUES(016, 'Nádia Fontainhas','2005-11-08', '[phone]',10 , '331267972',7894561137 , 0002, 209 )
INSERT INTO COLONO VALUES(017, 'Gustavo Jacinto','2005-11-08', '[phone]',11 , '335678972', 7894563337, 00023, 209 )
INSERT INTO COLONO VALUES(018, 'Guilherme Jacinto','2004-11-08', '[phone]',11 , '339867972',7834563337 , 00024, 209 )
INSERT INTO COLONO VALUES(019, 'Julio César','2004-11-08', '[phone]',12 , '339871972', 7894463337,00025 , 209 )
INSERT INTO COLONO VALUES(020, 'Vicente Alentejano','2003-11-08', '[phone]',10 , '334598972', 4894563337,00026 , 209 )
INSERT INTO COLONO VALUES(021, 'Camarão Samarão', '2007-10-08', '[phone]', 8, '223178972', 7394002337, 00027, 212)
INSERT INTO COLONO VALUES(022, 'João Louco', '2007-01-08', '[phone]',9 , '338178972',7894568907 ,00028 , 212)
INSERT INTO COLONO VALUES(023, 'Bernardo Soares', '2007-02-08', '[phone]', 8, '334178923', 7895673337, 00029, 212)
INSERT INTO COLONO VALUES(024, 'Fernando Pessoa', '2007-03-08', '[phone]',9 , '323178987',7856763337 ,00030 , 212)
INSERT INTO COLONO VALUES(025, 'Álvaro Campos','2007-04-08', '[phone]', 8, '323178919',7894756337 , 00031, 212)
INSERT INTO COLONO VALUES(026, 'Timon Pumba', '2007-05-08', '[phone]',9 , '334133972',7894564537 ,00032 , 212)
INSERT INTO COLONO VALUES(027, 'Simba Leão', '2007-07-13', '[phone]',9 , '133122472',7894243337 , 00033, 210)
INSERT INTO COLONO VALUES(028, 'Saphira Silva', '2007-04-01', '[phone]', 8, '113144574',7894772337 ,00034 , 210)
INSERT INTO COLONO VALUES(029, 'Layla Fontainhas', '2007-09-09', '[phone]',9 , '399878971',7894522237 ,00035 , 210)
INSERT INTO COLONO VALUES(030, 'Sasuke Uchiha', '2007-10-08', '[phone]',8 , '333188878', 7894563222, 00036, 210)
INSERT INTO COLONO VALUES(031, 'Itachi Uchiha', '2007-11-04', '[phone]',9 , '333199979', 7894563999, 00037, 210)
INSERT INTO COLONO VALUES(032, 'Kagome Higurashi','2007-11-03', '[phone]', 8, '323478911', 7894786337,00038 , 210)
INSERT INTO COLONO VALUES(033, 'Moroha Higurashi', '2007-02-11', '[phone]', 9, '367878922',7878663337 ,00039 , 210)
INSERT INTO COLONO VALUES(034, 'Ash Pikachu', '2007-11-01', '[phone]', 8, '333177862', 7894563567, 00040, 210)
INSERT INTO COLONO VALUES(7024,'Oscar Rabaça','2005-02-23','[phone]',9,'838697521',3498336745,17022,102)
INSERT INTO COLONO VALUES(7025,'Rodrigo Raimundo','2005-07-26','[phone]',10,'78997521',4411536745,17023,102)
INSERT INTO COLONO VALUES(7026,'Leonardo Conceicao','2009-12-28','[phone]',7,'22697521',7498536105,17024,101)
INSERT INTO COLONO VALUES(7027,'Carolina Conceicao','2009-12-28','[phone]',6,'63633721',3478636244,17024,101)
INSERT INTO COLONO VALUES(7028,'Francisco Pina','2007-01-28','[phone]',8,'37554521',3722536173,17025,101)
INSERT INTO COLONO VALUES(7029,'David Luis','2009-04-04','[phone]',6,'71197521',7498536746,17026,101)
INSERT INTO COLONO VALUES(7030,'Mariana Rabaça','2009-08-21','[phone]',6,'95803521',9498537468,17022,101)
INSERT INTO COLONO VALUES(7031,'Vasco Pereira','2005-03-16','[phone]',10,'15697443',3444536744,17027,101)
INSERT INTO COLONO VALUES(7032,'Andreia Pires','2005-08-29','[phone]',9,'99697221',3498561953,17028,101)
INSERT INTO COLONO VALUES(7033,'Leandro Antunes','2004-11-27','[phone]',11,'53697421',7498536745,17029,103)
INSERT INTO COLONO VALUES(7034,'Carla Nunes', '2005-03-24','[phone]',10,'63697651',3478636644,17030,103)
INSERT INTO COLONO VALUES(7035,'Francisca Chorão','2004-03-18','[phone]',11,'37976521',3798536173,17031,103)
INSERT INTO COLONO VALUES(7036,'Rui Prata','2003-10-24','[phone]',10,'74692221',7498546742,17032,103)
INSERT INTO COLONO VALUES(7037,'José Aguiar','2005-11-30','[phone]',9,'95753521',9496837468,17033,103)
INSERT INTO COLONO VALUES(7038,'Nuno Correia','2003-06-26','[phone]',12,'15697426',3463536722,17034,103)
INSERT INTO COLONO VALUES(7039,'Rosa Rosado','2003-05-09','[phone]',11,'99697233',3498361953,17035,103)
INSERT INTO COLONO VALUES(7040,'Violeta Gonçalves','2005-12-30','[phone]',9,'96697521',7490236766,17036,103)
INSERT INTO COLONO VALUES(7041,'Nuria Ferrão','2004-09-10','[phone]',11,'33693321',7498530745,17037,103)

Answer 1

当执行插入时，会失败，如：

INSERT INTO COLONO (numero, nome, dtnascimento, escolaridade, eeducacao, equipa) values (2,'test', '2020-11-22', 1, 1, 1);

出现错误：The INSERT statement conflicted with the CHECK constraint "CK__COLONO__dtnascim__2E26C93A". The conflict occurred in database "TEST", table "dbo.COLONO", column 'dtnascimento'.

一个人总是可以做到：

SELECT CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_NAME='CK__COLONO__dtnascim__2E26C93A';

输出：

CHECK_CLAUSE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(datediff(year,[dtnascimento],getdate())>=(6) AND datediff(year,[dtnascimento],getdate())<=(17))
(1 row affected)

这意味着(datediff(year.....语句是插入失败的原因。

Answer 2

马克思，

我看不出您表中的CHECK语句有任何错误。请在此处查看dbfiddle；它按预期工作，因为您尝试INSERT的值违反了CHECK语句，将无法工作。

如果您单击链接并向下滚动，您会看到根据您提供的数据，函数无法为其中一些行返回1。也就是说，这里有一个失败的例子：

INSERT INTO COLONO VALUES (368,'Matilde Borges','2013-03-20' /* dtnascimento */,
'[phone]',6,'73250721',8286872705,35905,22 /* equipa */)

当我们遍历您的dbo.grupo6函数时，我们看到equipa.numero=22，这会产生grupo=júniores，这需要年份在11和14之间。但是，从今天到所提供的日期之间有7年，这未通过CHECK。

在执行INSERT语句时，没有一种方法可以真正捕获它的值(至少，在不可能使用TRIGGERS或TRY-CATCH来尝试评估答案的情况下(。如果其中一个失败，则所有INSERTS都将失败。如果数据模型是灵活的，您可以考虑使用一个位列来存储结果，从而允许您在记录函数检查值的同时执行INSERT。然后，您可以查询那些在该函数中有或没有1的函数，并进行进一步调查。