How to handel MSSQL Constraints fail-CodePudding

I am new in sql. I have this table and I have created some constrains in the table:

Create Table Instructor(

InstructorID Integer NOT NULL Identity(0,1),
FirstName Varchar(30) NOT NULL,
LastName Varchar(40) NOT NULL,
DateOfBirth DATE NOT NULL,
InstructorAddress Varchar(256) NOT NULL,
PhoneNumber Varchar(40) NULL,
JobTitle Varchar(40) NOT NULL,
PayGrade Float NOT NULL,
ChargingFee Float NULL,

CONSTRAINT InstructorPK PRIMARY KEY(InstructorID),
CONSTRAINT ValidBirthYearInstructor CHECK( (DATEDIFF(year, DateOfBirth, getdate())>18) 
or (DATEDIFF(year, DateOfBirth, getdate())=18 and DATEDIFF(month, DateOfBirth, getdate())>0) 
or (DATEDIFF(year, DateOfBirth, getdate())=18 AND DATEDIFF(month, DateOfBirth, getdate())=0) AND DATEDIFF(day, DateOfBirth, getdate())=0)

);

This is the insert query:

INSERT INTO Instructor (FirstName, LastName, DateOfBirth, InstructorAddress, PhoneNumber, JobTitle, PayGrade, ChargingFee)
VALUES 
    ('Marcus','Jhon','2000-06-12','Std. Scuba Tank, Yellow',' 407771231241','Fitness instructor',301.40,30.2),
    ('Marcus','Jhon','2000-06-12','Std. Scuba Tank, Yellow',' 407771231241','Fitness instructor',301.40,30.2),
    ('Marcus','Jhon','2000-06-12','Std. Scuba Tank, Yellow',' 407771231241','Fitness instructor',301.40,30.2);

and if I will put a DateOfBirth that is under 18 years old it will give me this error: The INSERT statement conflicted with the CHECK constraint "ValidBirthYearInstructor". The conflict occurred in database "HEALTH CLUB Database", table "dbo.Instructor", column 'DateOfBirth'. I understand that the error is coming form the fact that I insert a person under 18 years old, but it will give me this error for all and it will insert nothing.

What can I do to be able to have an error and to have all the other rows inserted?

CodePudding user response：

The problem is that you are entering all three rows in the same transaction. When part of the transaction fails everything is rolled back and nothing is entered. If you run separate transactions then only the inserts which are wrong will fail.
Here I have separated the failing insert from the others and we see that the others are correctly inserted.
May I suggest that you research COMMIT and ROLLBACK?

✓

Create Table Instructor(

InstructorID Integer NOT NULL Identity(0,1),
FirstName Varchar(30) NOT NULL,
LastName Varchar(40) NOT NULL,
DateOfBirth DATE NOT NULL,
InstructorAddress Varchar(256) NOT NULL,
PhoneNumber Varchar(40) NULL,
JobTitle Varchar(40) NOT NULL,
PayGrade Float NOT NULL,
ChargingFee Float NULL,

CONSTRAINT InstructorPK PRIMARY KEY(InstructorID),
CONSTRAINT ValidBirthYearInstructor CHECK( (DATEDIFF(year, DateOfBirth, getdate())>18) 
or (DATEDIFF(year, DateOfBirth, getdate())=18 and DATEDIFF(month, DateOfBirth, getdate())>0) 
or (DATEDIFF(year, DateOfBirth, getdate())=18 AND DATEDIFF(month, DateOfBirth, getdate())=0) AND DATEDIFF(day, DateOfBirth, getdate())=0)

);
GO

✓

INSERT INTO Instructor (FirstName, LastName, DateOfBirth, InstructorAddress, 
PhoneNumber, JobTitle, PayGrade, ChargingFee)
VALUES 
    ('Marcus','Jhon','2000-06-12','Std. Scuba Tank, Yellow',' 407771231241',
    'Fitness instructor',301.40,30.2),
    ('Marcus','Jhon','2000-06-12','Std. Scuba Tank, Yellow',' 407771231241',
    'Fitness instructor',301.40,30.2);
    INSERT INTO Instructor (FirstName, LastName, DateOfBirth, InstructorAddress, PhoneNumber, JobTitle, PayGrade, ChargingFee)
VALUES 
    ('Marcus','Jhon','2020-06-12','Std. Scuba Tank, Yellow',' 407771231241',
    'Fitness instructor',301.40,30.2);

GO

Msg 547 Level 16 State 0 Line 8

The INSERT statement conflicted with the CHECK constraint "ValidBirthYearInstructor". The conflict occurred in database "fiddle_14c927218ed9489da7a7e8aef1171416", table "dbo.Instructor", column 'DateOfBirth'. Msg 3621 Level 0 State 0 Line 8 The statement has been terminated.

SELECT * FROM Instructor;
GO

InstructorID | FirstName | LastName | DateOfBirth | InstructorAddress       | PhoneNumber   | JobTitle           | PayGrade | ChargingFee
-----------: | :-------- | :------- | :---------- | :---------------------- | :------------ | :----------------- | -------: | ----------:
           0 | Marcus    | Jhon     | 2000-06-12  | Std. Scuba Tank, Yellow |  407771231241 | Fitness instructor |    301.4 |        30.2
           1 | Marcus    | Jhon     | 2000-06-12  | Std. Scuba Tank, Yellow |  407771231241 | Fitness instructor |    301.4 |        30.2

db<>fiddle here

CodePudding user response：

You can use a TRIGGER to supres unwanted rows.

I reduced the WHERE clause, but you must use the syntax of the check constarint

CREATE TRIGGER trg_instrturctor_audit
ON Instructor
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Instructor (FirstName, LastName, DateOfBirth, InstructorAddress, PhoneNumber, JobTitle, PayGrade, ChargingFee)
    SELECT FirstName, LastName, DateOfBirth, InstructorAddress, PhoneNumber, JobTitle, PayGrade, ChargingFee FROM inserted i
    WHERE DATEDIFF(year, i.DateOfBirth, getdate())>= 18

END

INSERT INTO Instructor (FirstName, LastName, DateOfBirth, InstructorAddress, PhoneNumber, JobTitle, PayGrade, ChargingFee)
VALUES 
    ('Marcus','Jhon','2000-06-12','Std. Scuba Tank, Yellow',' 407771231241','Fitness instructor',301.40,30.2),
    ('Marcus','Jhon','2000-06-12','Std. Scuba Tank, Yellow',' 407771231241','Fitness instructor',301.40,30.2),
    ('Marcus','Jhon','2018-06-12','Std. Scuba Tank, Yellow',' 407771231241','Fitness instructor',301.40,30.2);

GO

3 rows affected

SELECT * FROM Instructor
GO

InstructorID | FirstName | LastName | DateOfBirth | InstructorAddress       | PhoneNumber   | JobTitle           | PayGrade | ChargingFee
-----------: | :-------- | :------- | :---------- | :---------------------- | :------------ | :----------------- | -------: | ----------:
           0 | Marcus    | Jhon     | 2000-06-12  | Std. Scuba Tank, Yellow |  407771231241 | Fitness instructor |    301.4 |        30.2
           1 | Marcus    | Jhon     | 2000-06-12  | Std. Scuba Tank, Yellow |  407771231241 | Fitness instructor |    301.4 |        30.2

db<>fiddle here