By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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)
);
INSERT INTO GRUPO VALUES('iniciados',6,10)
INSERT INTO GRUPO VALUES('séniores',15,17)
INSERT INTO GRUPO VALUES('júniores',11,14)
3 rows affected
CREATE TABLE EQUIPA (
numero INT,
grupo NVARCHAR(25) NOT NULL,
designacao NVARCHAR(150) NOT NULL,
SOMEUNKNOWNVALUEHERE INT, -- for values like 8100, which didn't map to a column
PRIMARY KEY (numero),
FOREIGN KEY (grupo) REFERENCES GRUPO(nome),
);
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)
12 rows affected
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
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),
SOMEUNKNOWNVALUEHERE INT, -- for values like 13000, which didn't map to a column
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)
);
-- These work just fine
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)
14 rows affected
-- This is one of many of your rows that generates the error you described
INSERT INTO COLONO VALUES
(368,'Matilde Borges','2013-03-20' /* dtnascimento */,'+351922657141',6,'73250721',8286872705,35905,22 /* equipa */)
Msg 547 Level 16 State 0 Line 2
The INSERT statement conflicted with the CHECK constraint "CK__COLONO__46E78A0C". The conflict occurred in database "fiddle_edd65cb4c77d49e6a090599a0ad79f23", table "dbo.COLONO".
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.
-- When we run this function normally, it does NOT generate a 1 as you said it would;
-- thus, it fails the CHECK statement
select dbo.get_grupo6(22 /*equipa*/,DATEDIFF(year, '2013-03-20' /*dtnascimento*/, GETDATE()))
-- It returns 0 because there are 7 years between this date and today
-- however, the equipa.numero = 22, which goes to grupo júniores; the min value is 11 and the max value is 14
| (No column name) |
|---|
| 0 |