I want to compare the output of a scalar function with a scalar value in a CHECK statement when creating a table, but it doesn't seem to do the right comparisons. I want the check statement to be "checked" if the scalar function returns 1. Is there any way to do this? (The function is called in the last check statement)
The statement runs successfully, and if I run the function out of the CHECK function it returns the right values (0 or 1), but when I insert data that satisfies 1 in the function returned value, errors are returned. This is my code and what I've tried,
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)
);
My function get_grupo6 is
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
The creation of grupo table is as follows
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)
);
The creation of equipa table follows
CREATE TABLE EQUIPA (
numero INT,
grupo NVARCHAR(25) NOT NULL,
designacao NVARCHAR(150) NOT NULL,
PRIMARY KEY (numero),
FOREIGN KEY (grupo) REFERENCES GRUPO(nome),
);
The following errors are returned when I insert data, even though the function is returning the values I am expecting it to return (no error should be returned):
Msg 547, Level 16, State 0, Line 219 The INSERT statement conflicted with the CHECK constraint "CK__COLONO__110145FE". The conflict occurred in database "master", table "dbo.COLONO". The statement has been terminated. Msg 547, Level 16, State 0, Line 220 The INSERT statement conflicted with the CHECK constraint "CK__COLONO__110145FE". The conflict occurred in database "master", table "dbo.COLONO".
Also, here is what I inserted,
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','+351926276375',6,'13845623',5053316251,13000,22)
INSERT INTO COLONO VALUES(998,'Luis Silva','2008-01-01','+351935247523',6,'4529788',6116883976,14000,22)
INSERT INTO COLONO VALUES(663,'Ricardo Morgado','2007-08-22','+351928747601',6,'84705373',2546154599,11040,22)
INSERT INTO COLONO VALUES(937,'Luis Moreira','2013-06-12','+351965121631',6,'18062006',6079199549,55672,20)
INSERT INTO COLONO VALUES(837,'Carolina Esquivel','2013-03-03','+351998773426',6,'10600058',6295896573,33332,21)
INSERT INTO COLONO VALUES(120,'Carolina Correia','2011-03-03','+351977595939',6,'53421641',7604024824,44432,21)
INSERT INTO COLONO VALUES(744,'Carolina Esquivel','2009-01-29','+351969113814',6,'49154963',7069630161,22123,22)
INSERT INTO COLONO VALUES(936,'Luisa Coutinho','2011-03-20','+351931314073',6,'88893561',2981938455,44123,20)
INSERT INTO COLONO VALUES(764,'Isabel Paiva','2009-03-20','+351954414885',6,'59615621',3874426665,11123,22)
INSERT INTO COLONO VALUES(285,'Maria Da Vincci','2005-03-20','+351917588569',6,'53130407',9399759588,52695,23)
INSERT INTO COLONO VALUES(257,'Luis Dorei','2006-03-20','+351998893333',6,'72302344',4865860563,43889,22)
INSERT INTO COLONO VALUES(988,'Noa Vicente','2007-03-20','+351940079695',6,'44766751',7309911603,53333,22)
INSERT INTO COLONO VALUES(234,'Darwin James','2004-03-20','+351981854595',6,'48034937',4137784431,85695,23)
INSERT INTO COLONO VALUES(667,'Cecilia Borges','2009-03-20','+351948657621',6,'73250728',8286872702,35905,22)
INSERT INTO COLONO VALUES(368,'Matilde Borges','2013-03-20','+351922657141',6,'73250721',8286872705,35905,22)
INSERT INTO COLONO VALUES(167,'Najeli Kingston','2013-03-20','+351938657641',6,'73250722',8286872742,35906,22)
INSERT INTO COLONO VALUES(745,'Abdul Mallade','2013-03-20','+351938657648',6,'73250724',8286872744,35907,22)
INSERT INTO COLONO VALUES(7008,'Barbara Guimar','2005-02-26','+351978632254',10,'35697521',3498536745,17008,100)
INSERT INTO COLONO VALUES(7009,'Miguel Barbosa','2004-12-16','+351963433754',11,'35697721',3498636644,17009,100)
INSERT INTO COLONO VALUES(7010,'Rafael Fernandes','2003-01-30','+351924632244',12,'35557521',3498536473,17010,100)
INSERT INTO COLONO VALUES(7011,'Rubem Santos','2004-06-26','+351963632299',11,'35287521',3498536742,17011,100)
INSERT INTO COLONO VALUES(7012,'Afonso Santos','2004-06-26','+351913632254',11,'35707521',3498536741,17011,100)
INSERT INTO COLONO VALUES(7013,'Pedro Gomes','2005-09-13','+351963667254',10,'35697211',3498536740,17012,100)
INSERT INTO COLONO VALUES(7014,'André Gomes','2005-09-13','+351965632254',10,'35692228',3498536953,17012,100)
INSERT INTO COLONO VALUES(7015,'Daniel Fonseca','2003-08-28','+351963633544',11,'35897521',3498565674,17013,100)
INSERT INTO COLONO VALUES(7016,'Maria Vermelho','2005-12-08','+351913632233',9,'53697521',7498536715,17014,102)
INSERT INTO COLONO VALUES(7017,'Célia Norton','2003-10-31','+351913633754',11,'63697721',3478636632,17015,102)
INSERT INTO COLONO VALUES(7018,'Catarina Pina','2003-11-08','+351914632214',12,'37557521',3798536473,17016,102)
INSERT INTO COLONO VALUES(7019,'Carolina Milheiro','2004-05-25','+351913632234',8,'74697521',7498536742,17017,102)
INSERT INTO COLONO VALUES(7020,'Margarida Borges','2004-07-22','+351913636224',11,'95707521',9498536741,17018,102)
INSERT INTO COLONO VALUES(7021,'Raquel Gomes','2005-03-16','+351913667254',10,'15697421',3494536740,17019,102)
INSERT INTO COLONO VALUES(7022,'Andreia Pires','2005-08-29','+351915632254',9,'69697221',3498566953,17020,102)
INSERT INTO COLONO VALUES(7023,'João Amaro','2003-07-28','+351913633544',12,'25897521',3498545674,17021,102)
INSERT INTO COLONO VALUES(008, 'Maria Silva','2009-07-12', '+351963456678',7, '314154172',7894561239,00012,211)
INSERT INTO COLONO VALUES(007, 'Helena Luz','2008-07-10', '+351963879678',8, '232657172',7894561237 ,00014 , 211)
INSERT INTO COLONO VALUES(009, 'Lisa Ye','2008-09-01', '+351963456345',9, '289154172',7894561232 ,00015, 211)
INSERT INTO COLONO VALUES(010, 'Diana Fonseca', '2008-08-12', '+351963456132',8, '276159672', 7894565837, 00016, 211)
INSERT INTO COLONO VALUES(011, 'Beatriz Coimbra','2007-07-11', '+351963645678',7,'245169172', 7894577737,00017 , 211)
INSERT INTO COLONO VALUES(012, 'Beatriz Godinho', '2007-06-10', '+351963753267',7, '333178972',7894563237 , 00018, 211)
INSERT INTO COLONO VALUES(013, 'André Churrasqueira','2005-11-08','+351963456999', 9, '339727685',7894561337 , 00019, 209 )
INSERT INTO COLONO VALUES(014, 'Francisco Fonseca','2005-11-08', '+351963456777',9 , '333369272',7894564437 ,00020 , 209 )
INSERT INTO COLONO VALUES(015, 'Jan Emanuel','2005-11-08', '+351963456444',10 , '333345672',7894563937 ,00021 , 209 )
INSERT INTO COLONO VALUES(016, 'Nádia Fontainhas','2005-11-08', '+351963456333',10 , '331267972',7894561137 , 0002, 209 )
INSERT INTO COLONO VALUES(017, 'Gustavo Jacinto','2005-11-08', '+351963456134',11 , '335678972', 7894563337, 00023, 209 )
INSERT INTO COLONO VALUES(018, 'Guilherme Jacinto','2004-11-08', '+351963456888',11 , '339867972',7834563337 , 00024, 209 )
INSERT INTO COLONO VALUES(019, 'Julio César','2004-11-08', '+351963666777',12 , '339871972', 7894463337,00025 , 209 )
INSERT INTO COLONO VALUES(020, 'Vicente Alentejano','2003-11-08', '+351963456088',10 , '334598972', 4894563337,00026 , 209 )
INSERT INTO COLONO VALUES(021, 'Camarão Samarão', '2007-10-08', '+351963222345', 8, '223178972', 7394002337, 00027, 212)
INSERT INTO COLONO VALUES(022, 'João Louco', '2007-01-08', '+351961116345',9 , '338178972',7894568907 ,00028 , 212)
INSERT INTO COLONO VALUES(023, 'Bernardo Soares', '2007-02-08', '+351961156345', 8, '334178923', 7895673337, 00029, 212)
INSERT INTO COLONO VALUES(024, 'Fernando Pessoa', '2007-03-08', '+351963226111',9 , '323178987',7856763337 ,00030 , 212)
INSERT INTO COLONO VALUES(025, 'Álvaro Campos','2007-04-08', '+351963477345', 8, '323178919',7894756337 , 00031, 212)
INSERT INTO COLONO VALUES(026, 'Timon Pumba', '2007-05-08', '+351963333222',9 , '334133972',7894564537 ,00032 , 212)
INSERT INTO COLONO VALUES(027, 'Simba Leão', '2007-07-13', '+351963234456',9 , '133122472',7894243337 , 00033, 210)
INSERT INTO COLONO VALUES(028, 'Saphira Silva', '2007-04-01', '+351967488345', 8, '113144574',7894772337 ,00034 , 210)
INSERT INTO COLONO VALUES(029, 'Layla Fontainhas', '2007-09-09', '+351963089345',9 , '399878971',7894522237 ,00035 , 210)
INSERT INTO COLONO VALUES(030, 'Sasuke Uchiha', '2007-10-08', '+351963456666',8 , '333188878', 7894563222, 00036, 210)
INSERT INTO COLONO VALUES(031, 'Itachi Uchiha', '2007-11-04', '+351963646345',9 , '333199979', 7894563999, 00037, 210)
INSERT INTO COLONO VALUES(032, 'Kagome Higurashi','2007-11-03', '+351963445345', 8, '323478911', 7894786337,00038 , 210)
INSERT INTO COLONO VALUES(033, 'Moroha Higurashi', '2007-02-11', '+351963996345', 9, '367878922',7878663337 ,00039 , 210)
INSERT INTO COLONO VALUES(034, 'Ash Pikachu', '2007-11-01', '+351923456234', 8, '333177862', 7894563567, 00040, 210)
INSERT INTO COLONO VALUES(7024,'Oscar Rabaça','2005-02-23','+351993664254',9,'838697521',3498336745,17022,102)
INSERT INTO COLONO VALUES(7025,'Rodrigo Raimundo','2005-07-26','+351983632254',10,'78997521',4411536745,17023,102)
INSERT INTO COLONO VALUES(7026,'Leonardo Conceicao','2009-12-28','+351913632274',7,'22697521',7498536105,17024,101)
INSERT INTO COLONO VALUES(7027,'Carolina Conceicao','2009-12-28','+351813633754',6,'63633721',3478636244,17024,101)
INSERT INTO COLONO VALUES(7028,'Francisco Pina','2007-01-28','+351914632254',8,'37554521',3722536173,17025,101)
INSERT INTO COLONO VALUES(7029,'David Luis','2009-04-04','+351923623654',6,'71197521',7498536746,17026,101)
INSERT INTO COLONO VALUES(7030,'Mariana Rabaça','2009-08-21','+351965639154',6,'95803521',9498537468,17022,101)
INSERT INTO COLONO VALUES(7031,'Vasco Pereira','2005-03-16','+351926667254',10,'15697443',3444536744,17027,101)
INSERT INTO COLONO VALUES(7032,'Andreia Pires','2005-08-29','+351925257254',9,'99697221',3498561953,17028,101)
INSERT INTO COLONO VALUES(7033,'Leandro Antunes','2004-11-27','+351923492274',11,'53697421',7498536745,17029,103)
INSERT INTO COLONO VALUES(7034,'Carla Nunes', '2005-03-24','+351965793754',10,'63697651',3478636644,17030,103)
INSERT INTO COLONO VALUES(7035,'Francisca Chorão','2004-03-18','+351924862254',11,'37976521',3798536173,17031,103)
INSERT INTO COLONO VALUES(7036,'Rui Prata','2003-10-24','+351961023654',10,'74692221',7498546742,17032,103)
INSERT INTO COLONO VALUES(7037,'José Aguiar','2005-11-30','+351965630254',9,'95753521',9496837468,17033,103)
INSERT INTO COLONO VALUES(7038,'Nuno Correia','2003-06-26','+351920007254',12,'15697426',3463536722,17034,103)
INSERT INTO COLONO VALUES(7039,'Rosa Rosado','2003-05-09','+351920057254',11,'99697233',3498361953,17035,103)
INSERT INTO COLONO VALUES(7040,'Violeta Gonçalves','2005-12-30','+351913603074',9,'96697521',7490236766,17036,103)
INSERT INTO COLONO VALUES(7041,'Nuria Ferrão','2004-09-10','+351913031274',11,'33693321',7498530745,17037,103)
Marx,
I see no error with the CHECK statement in your table. See the dbfiddle here; it is working as intended, because the value you are trying to INSERT violates the CHECK statement and will not work.
If you click the link and scroll down, you see that based on the data you provided, there is no way for the function to return 1 for some of these rows. Namely, here is one example that fails:
INSERT INTO COLONO VALUES (368,'Matilde Borges','2013-03-20' /* dtnascimento */,
'+351922657141',6,'73250721',8286872705,35905,22 /* equipa */)
When we step through your dbo.grupo6 function, we see that that equipa.numero = 22, which brings up grupo = júniores, which requires the years to be between 11 and 14. However, there are 7 years between today and the date provided, which fails the CHECK.
There isn't a way to really capture the value of this when performing the INSERT statement (at least, not without possibly using TRIGGERS or a TRY-CATCH to attempt to evaluate the answer). If one fails, all the INSERTS will fail. If the data model is flexible, you could consider having a bit column to store the result of this, thereby allowing you to do INSERTs while recording the value of your functional check. Then you could query against those that did or did not have a 1 in this function, and investigate further.