|
|
Error Message
Server: Msg 333, Level 16, State 1, Line 1
The target table '<Table Name>' of the OUTPUT INTO
clause cannot have any enabled check constraints or any
enabled rules.
Found check constraint or rule '<Check Constraint Name>'.
Causes
The OUTPUT clause, introduced in SQL Server 2005, returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE or MERGE statement.
|
These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
One of the restrictions when using the OUTPUT clause is that if the result of the OUTPUT clause is inserted into a table, the table cannot have a CHECK constraint or enabled rules; otherwise this error message will be encountered.
To illustrate how this error is generated, given the following tables structures:
CREATE TABLE [dbo].[StudentGrade] (
[StudentID] INT,
[Course] VARCHAR(10),
[Grade] INT CONSTRAINT CK_Grade CHECK ( [Grade] BETWEEN 0 AND 100 )
)
CREATE TABLE [dbo].[StudentGradeHistory] (
[StudentID] INT,
[Course] VARCHAR(10),
[OldGrade] INT CONSTRAINT CK_OldGrade CHECK ( [OldGrade] BETWEEN 0 AND 100 ),
[NewGrade] INT CONSTRAINT CK_NewGrade CHECK ( [NewGrade] BETWEEN 0 AND 100 )
)
The grades of students for MATH101 is being increased by 5 points for those students who have a grade of less than 70 as they completed the extra credit assignment given to them. For each student grade increased, the old grade and new grade needs to be inserted to the student grade history table.
In fulfilling this requirement, the following UPDATE statement which includes the OUTPUT INTO clause is executed.
UPDATE [dbo].[StudentGrade]
SET [Grade] = [Grade] + 5
OUTPUT [inserted].[StudentID], [inserted].[Course], [deleted].[Grade], [inserted].[Grade]
INTO [dbo].[StudentGradeHistory]
WHERE [Grade] < 70 AND [Course] = 'MATH101'
But since the target table, in this case the [dbo].[StudentGradeHistory] table, has a CHECK constraint on both the OldGrade and NewGrade columns, the following error message is encountered:
Msg 333, Level 16, State 1, Line 3
The target table 'dbo.StudentGradeHistory' of the OUTPUT INTO clause cannot have any enabled
check constraints or any enabled rules. Found check constraint or rule 'CK_OldGrade'.
Solution / Work Around:
As mentioned earlier, and as the message suggests, the target table of the OUTPUT INTO clause cannot have a CHECK constraint or enabled rules. To overcome this restriction, one thing that can be done is to insert the updated student records together with the old and new grades into a temporary table or a table variable. Then after the UPDATE statement, the updated student grades can now be inserted into the student grade history table.
The following script can be used to replace the UPDATE statement earlier which inserts all updated student records together with their old and new grades after the UPDATE operation.
DECLARE @StudentGradeHistory TABLE (
[StudentID] INT,
[Course] VARCHAR(10),
[OldGrade] INT,
[NewGrade] INT
)
UPDATE [dbo].[StudentGrade]
SET [Grade] = [Grade] + 5
OUTPUT [inserted].[StudentID], [inserted].[Course], [deleted].[Grade], [inserted].[Grade]
INTO @StudentGradeHistory
WHERE [Grade] < 70 AND [Course] = 'MATH101'
INSERT INTO [dbo].[StudentGradeHistory] ( [StudentID], [Course], [OldGrade], [NewGrade] )
SELECT [StudentID], [Course], [OldGrade], [NewGrade]
FROM @StudentGradeHistory
The first part of the script is the declaration of a table variable that will contain the student ID of the student grades updated together with the old grade and new grade. This table variable is then used as the target table for in the OUTPUT INTO clause of the UPDATE statement.
Lastly, all updated student grades that are now in the table variable can now be inserted to the student grade history table without generating this error.
|