check constraint custom message

Question

Hi. Is there a way to perform data validation using CHECK constraints in t-sql and to show somehow the data which did not pass the check constraint?

Answer 1

You can run a select that uses the logic from your check constraint on the table the constraint is defined on.

The returned rows would be the ones that would not pass validation.

If you define the following table and CHECK constraint:

IF OBJECT_ID ('dbo.Vendors', 'U') IS NOT NULL
DROP TABLE dbo.Vendors;
GO
CREATE TABLE dbo.Vendors 
    (VendorID int PRIMARY KEY, VendorName nvarchar (50), 
    CreditRating tinyint)
GO
ALTER TABLE dbo.Vendors ADD CONSTRAINT CK_Vendor_CreditRating
    CHECK (CreditRating >= 1 AND CreditRating <= 5)

You can select the rows this way:

SELECT VendorID
FROM dbo.Vendors
WHERE (CreditRating >= 1 AND CreditRating <= 5)

Answer 2

Is there a way to perform data validation using CHECK constraints...

Yes, that is what CHECK constraints are for: Implementing data validation that can't be done through table modelling

...and to show somehow the data which did not pass the check constraint?

No, the only information you will get back from CHECK constraints is an error message when provided data has failed to meet the CHECK constraint requirements. It's up to you to review the data.

A trigger would be the next likely option, to have the ability to redirect the attempted data to a table for logging.