Introduction
From one of my friends, I got a mail that contains an error message related to CHECK constrain and that is CHECK constraint - referencing another column gives an error.
Msg 8141, Level 16, State 0, Line 1
Column CHECK constraint for column 'Col Name' references another column, table 'Table Name'
To understand it properly let me take an example.
Step – 1 [ Create a Reference Master Table and Inserting Records ]
IF OBJECT_ID(N'dbo.tbl_GradeMaster', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_GradeMaster]
END
GO
CREATE TABLE [dbo].[tbl_GradeMaster]
(EMPGRADE CHAR(1) NOT NULL PRIMARY KEY,
MINSAL DECIMAL(20,2)NOT NULL);
-- Inserting Records
INSERT INTO [dbo].[tbl_GradeMaster]
(EMPGRADE, MINSAL)
VALUES ('A', 7000.00),
('B', 5000.00),
('C', 3000.00);
Step – 2 [ Create a Function that is used by CHECK Constraint ]
-- Craeting Function
IF OBJECT_ID(N'dbo.func_EMPSAL_GRADWISE', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[func_EMPSAL_GRADWISE];
END
GO
CREATE FUNCTION [dbo].[func_EMPSAL_GRADWISE]
(
@p_EMPGRADE CHAR(1) = NULL
)
RETURNS DECIMAL(20,2)
AS
BEGIN
DECLARE @v_MINSAL DECIMAL(20,2)
SET @v_MINSAL = (SELECT MINSAL
FROM [dbo].[tbl_GradeMaster]
WHERE EMPGRADE = 'A');
RETURN @v_MINSAL;
END
GO
Step – 3 [ USE this Function in CHECK Constraint ]
-- CREATE Transaction Table
IF OBJECT_ID(N'dbo.tbl_EMPLOYEEDTLS', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_EMPLOYEEDTLS];
END
GO
CREATE TABLE [dbo].[tbl_EMPLOYEEDTLS]
(
EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPGRADE CHAR(1) NOT NULL,
EMPSAL DECIMAL(20,2) NOT NULL CHECK(dbo.func_EMPSAL_GRADWISE(EMPGRADE)>EMPSAL)
);
GO
--- [ Error ]
Msg 8141, Level 16, State 0, Line 1
Column CHECK constraint for column 'EMPSAL' references another column, table 'tbl_EMPLOYEEDTLS'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Step – 4 [ Solution -1 of Above Problem ]
-- [ Solutions -1 ]
IF OBJECT_ID(N'dbo.tbl_EMPLOYEEDTLS', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_EMPLOYEEDTLS];
END
GO
CREATE TABLE [dbo].[tbl_EMPLOYEEDTLS]
(
EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPGRADE CHAR(1) NOT NULL,
EMPSAL DECIMAL(20,2) NOT NULL,
CONSTRAINT CHK_EMPSAL_EMPGRADE CHECK(dbo.func_EMPSAL_GRADWISE(EMPGRADE)<=EMPSAL)
);
GO
-- INSERTING Records
INSERT INTO [dbo].[tbl_EMPLOYEEDTLS]
(EMPNAME, EMPGRADE, EMPSAL)
VALUES ('Joydeep Das', 'A', 7500);
(1 row(s) affected)
INSERT INTO [dbo].[tbl_EMPLOYEEDTLS]
(EMPNAME, EMPGRADE, EMPSAL)
VALUES ('Joydeep Das', 'A', 6000);
-- [ Violation of Check Constraint ]
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint
"CHK_EMPSAL_EMPGRADE". The conflict occurred in database
"FARVISIONDB", table "dbo.tbl_EMPLOYEEDTLS".
The statement has been terminated.
Step – 5 [ Solution -2 of Above Problem ]
-- [ Solutions -2 Create the Table First ]
IF OBJECT_ID(N'dbo.tbl_EMPLOYEEDTLS', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_EMPLOYEEDTLS];
END
GO
CREATE TABLE [dbo].[tbl_EMPLOYEEDTLS]
(
EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPGRADE CHAR(1) NOT NULL,
EMPSAL DECIMAL(20,2) NOT NULL
)
GO
-- [ Alter It and Add Check Constraint ]
ALTER TABLE [dbo].[tbl_EMPLOYEEDTLS]
ADD CONSTRAINT CHK_EMPSAL_EMPGRADE CHECK(dbo.func_EMPSAL_GRADWISE(EMPGRADE)<=EMPSAL);
-- Now check it again
INSERT INTO [dbo].[tbl_EMPLOYEEDTLS]
(EMPNAME, EMPGRADE, EMPSAL)
VALUES ('Joydeep Das', 'A', 7500);
(1 row(s) affected)
INSERT INTO [dbo].[tbl_EMPLOYEEDTLS]
(EMPNAME, EMPGRADE, EMPSAL)
VALUES ('Joydeep Das', 'A', 6000);
-- [ Violation of Check Constraint ]
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint
"CHK_EMPSAL_EMPGRADE". The conflict occurred in database
"FARVISIONDB", table "dbo.tbl_EMPLOYEEDTLS".
The statement has been terminated.
Hope you like it.
Pouted by: MR. JOYDEEP DAS
No comments:
Post a Comment