Many of us have experience on more than one SQL-DBMS, but it is almost inevitable that you will become a specialist on one product or another. All SQL-DBMSs are, of course, based on the relational model as originally defined by Edgar Codd (how well SQL follows the relational model is another issue,<\/a> which we won’t go into here). Though the basic principles of all products are the same, sometimes the details of particular implementations can lead to difficulties when you make the move from one product to another. I have worked with Sybase and Oracle in the past, but today I make my living with Microsoft SQL Server, so I am most up to date with that product and have the deepest knowledge of it.<\/p>\n It can be interesting to look at the way the different products solve the same problems, and to observe what features may be implemented in one and not in the other. One of these differences is the way that Oracle and SQL Server handle the reporting of multiple constraint violations from a single statement. The Oracle approach is elegant and straightforward, in SQL Server we have to put a lot of effort into devising a workaround. \u00a0<\/p>\n Oracle doesn’t always do better \u00a0in this sort of comparison; SQL Server specialists are often puzzled by the dire warning issued against using triggers by many Oracle experts. To those of you are familiar with SQL Server but not with Oracle, triggers in Oracle can be used effectively but it is considerably more challenging to use them correctly [1].<\/a><\/p>\n On the other hand Oracle specialists may be equally puzzled by what I am about to say about SQL Server.<\/p>\n For the most part SQL Server deals fairly well with set based operations, but with constraints in general, some serious shortcomings quickly become apparent. Any SQL insert, update or delete command will cause an exception condition to be raised that\u00a0 returns one constraint violation message, no matter how many constraints are violated, how many different types of constraint are violated,\u00a0 or how many of the affected rows violate the constraint.<\/p>\n Before we go any further, let’s have a closer look at the kind of problems that SQL Server’s standard constraint violation reporting leads to.<\/p>\n We will use the following small database design for the examples.<\/p>\n We now attempt the following insert:<\/p>\n Resulting in the following message:<\/p>\n Let’s say we now add the primary key reference to fix this problem:<\/p>\n Attempting the insert on sales_order_line again, we once again get a foreign key violation constraint message, but for a different foreign key.<\/p>\n <\/p>\n Only one key violation is reported at a time. The situation gets even worse if we are inserting multiple rows.\u00a0 First let’s fix the foreign key violation on product:<\/p>\n Now we attempt to insert several rows into sales_product_line, some of which are valid and some of which will violate foreign key constraints:<\/p>\n We observe that the insert values contain not just foreign key violations, but also primary key violations.<\/p>\n When issuing the insert command, as usual, only one of the constraint violations is reported:<\/p>\n If your database is at the size of this demonstration, it would seem absurd to consider that this is a difficulty. However, with the enormous amounts of data being routinely imported into, or captured by, databases, a constraint violation exception can lead to a great deal of subsequent forensic work to find the rows that did it.<\/p>\n Our Oracle user friends would probably say at this point that they don’t understand what the problem is. We just use the LOG ERRORS clause on the insert. This feature has been available since Oracle 10g Version 2.<\/p>\n I created our test database in Oracle and issued the following commands:<\/p>\n This creates a table to hold the errors with the same columns as sales_order_line and some additional columns to hold the error information.<\/p>\n We then issue the insert with the LOG ERRORS clause added at the end (the syntax has been adapted as Oracle does not support multiple value lines in the insert statement, also I had to shorten the constraint names as Oracle will only accept 30 characters):<\/p>\n This gives us the following result:<\/p>\n There are ways of avoiding this problem in SQL Server. Some workarounds are:<\/b><\/p>\n The first two solutions will work well if you are in control of the import process. However, this isn’t always the case. By using the INSTEAD OF trigger, users can use their existing ways of inserting, deleting or updating data. In addition we can get much closer to the behaviour of the elegant Oracle solution. Clearly this cannot be achieved in a standard trigger on the table, because the database update has already taken place before the trigger has run and, if any constraints are violated, then the\u00a0 trigger code will never be executed. <\/span><\/p>\n Here is the INSTEAD OF insert trigger to report all the constraint violations for the primary key and the two foreign key constraints in a global temporary table.<\/p>\n We now do the insert again: <\/span><\/p>\n We now get this result: <\/span><\/p>\n We can now execute the query from the RAISERROR message:<\/span><\/p>\n Which now gives us all the rows that violated constraints and the corresponding constraint name.<\/p>\n I have deliberately used temporary tables here as the we are probably only concerned with the results during the current session and we can save ourselves a lot of housekeeping by not holding the data permanently. Of course triggers for update and delete would also be required.<\/p>\n Now I can think of a number of objections to this approach.<\/p>\n Another refinement would be to have the INSTEAD OF triggers on views rather than on the tables themselves. If the view was updated then all the constraint violations would be reported, a direct update on the table would only report the first constraint on the first row that violated that constraint. Of course this would mean maintaining a whole set of views with the same structure as the underlying tables, but here again DDL triggers could come to our aid to modify the views automatically whenever the table structure changes (we would need a completely consistent naming convention for the views to achieve this).<\/p>\n As we have seen, different SQL-DBMS products can vary considerably in how they handle particular issues. Though it can be hard enough to keep up to date on the details of our “home” product, it can be very interesting to have a heads up from time to time and look at how the other half lives. Sometimes the grass on the other side of the fence is greener, but sometimes the concrete is grayer.<\/p>\n I think we can be fairly hopeful that Microsoft will eventually bring a similar kind of constraint reporting into the core product, thus matching the Oracle functionality, but until that time I hope that this article has provided some ideas about how a workaround could be developed.<\/p>\n [1] Oracle: Triggers Considered Harmful, Considered Harmful<\/a><\/p>\nThe Problem<\/h2>\n
CREATE TABLE dbo.customer\r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 customer_id int NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 customer_name varchar(100) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT \"customer id must be unique\" PRIMARY KEY \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 customer_id \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n);\r\n\r\nINSERT INTO dbo.customer \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 customer_id, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 customer_name\r\n)\r\nVALUES\r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Siemens'\r\n);\r\n\r\nCREATE TABLE dbo.sales_order(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 customer_id int NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number int NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_date date NOT NULL CONSTRAINT sales_order_date_default\u00a0 DEFAULT getdate(),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT \"sales order number must be unique\" PRIMARY KEY CLUSTERED \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT \"sales order must have belong to valid customer\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOREIGN KEY(customer_id)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REFERENCES dbo.customer (customer_id)\r\n);\r\n\r\nCREATE TABLE dbo.product(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 product_name varchar(100) NOT NULL,\r\n\u00a0CONSTRAINT \"product name must be unique\" PRIMARY KEY CLUSTERED \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 product_name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n);\r\n\r\n\r\nCREATE TABLE sales_order_line\r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number\u00a0 integer NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 product_name\u00a0\u00a0 varchar(100) NOT NULL, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 quantity\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer NOT NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT quantity_default DEFAULT 1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT \"order number and product name must be unique\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY (sales_order_number, product_name), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT \"sales order line must belong to valid order\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOREIGN KEY (sales_order_number) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REFERENCES sales_order (sales_order_number), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT \"sales order line must be for valid product\" \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOREIGN KEY (product_name) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REFERENCES product (product_name) \r\n);\r\n<\/pre>\n
insert into sales_order_Line \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 product_name, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 quantity\r\n)\r\nvalues \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Bolt', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100\r\n)\r\n<\/pre>\n
\t\tMsg 547, Level 16, State 0, Line 3\r\nThe INSERT statement conflicted with the FOREIGN KEY constraint \"sales order line must belong to valid order\". The conflict occurred in database \"playground\", table \"dbo.sales_order\", column 'sales_order_number'.\r\nThe statement has been terminated.\r\n<\/pre>\n
\t\tINSERT INTO sales_order \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 customer_id, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number \r\n)\r\nVALUES \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100\r\n)\r\n<\/pre>\n
insert into sales_order_Line \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 product_name, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 quantity\r\n)\r\nvalues \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Bolt', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100\r\n)\r\n<\/pre>\n
Msg 547, Level 16, State 0, Line 3\r\nThe INSERT statement conflicted with the FOREIGN KEY constraint \"sales order line must be for valid product\". The conflict occurred in database \"playground\", table \"dbo.product\", column 'product_name'.\r\nThe statement has been terminated.\r\n<\/pre>\n
INSERT INTO product (product_name) VALUES ('bolt') \r\n<\/pre>\ninsert into sales_order_Line \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 product_name, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 quantity\r\n)\r\nvalues (100,'Bolt',\u00a0 100),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (100, 'Bolt', 20), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (200, 'Bolt', 1), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (100, 'screw', 4) \r\n<\/pre>\n
Msg 2627, Level 14, State 1, Line 1\r\nViolation of PRIMARY KEY constraint 'order number and product name must be unique'. Cannot insert duplicate key in object 'dbo.sales_order_line'. The duplicate key value is (100, Bolt).\r\nThe statement has been terminated.\r\n<\/pre>\n
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('sales_order_line', 'err$_sales_order_line');\r\n<\/pre>\ninsert into sales_order_Line \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 product_name, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 quantity\r\n)\r\nselect\u00a0 100,'Bolt',\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100 from dual\r\nunion all \r\nselect 100, 'Bolt', 20 from dual\r\nunion all\u00a0 \r\nselect 200, 'Bolt', 1 from dual\r\nunion all\u00a0\u00a0 \r\nselect 100, 'screw', 4 from dual\r\nLOG ERRORS INTO err$_sales_order_line ('INSERT') REJECT LIMIT UNLIMITED;\r\n<\/pre>\nORA ERR NUMBERSORA ERR MESG$ORA ERR ROWIDSORA ERR OPTYPSORA ERR TAGSSALES ORDER NUMBERPRODUCT NAMEQUANTITY1ORA-00001: Unique Constraint (SYSTEM.order nr: prod name not unique) verletzt|INSERT100Bolt1001ORA-00001: Unique Constraint (SYSTEM.order nr: prod name not unique) verletzt|INSERT100Bolt202291ORA-02291: Integritats-Constraint (SYSTEM.sales ord line not valid order) verletzt - ubergeordneter Schlussel nicht gefunden|INSERT200Bolt12291ORA-02291: Integritats-Constraint (SYSTEM.sales ord line not valid prod) verletzt - ubergeordneter Schlussel nicht gefunden|INSERT100screw4<\/pre>\n
A Solution<\/h2>\n
\n
CREATE TRIGGER dbo_sales_order_line_constraint_check_insert ON dbo.sales_order_line INSTEAD OF INSERT \r\nAS\r\nDECLARE @error bit = 0\r\nIF object_id('tempdb..##err_dbo_sales_order_line') IS NULL\r\nBEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CREATE TABLE ##err_dbo_sales_order_line(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number\u00a0\u00a0 int NOT NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,product_name varchar(100) NOT NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,quantity\u00a0\u00a0\u00a0\u00a0 int NULL \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,spid integer NOT NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,constraint_name sysname\u00a0 NOT NULL)\r\nEND\r\nDELETE ##err_dbo_sales_order_line WHERE spid = @@spid\r\nINSERT INTO ##err_dbo_sales_order_line(sales_order_number,product_name,quantity, spid, constraint_name)\r\nSELECT\u00a0 sales_order_number,product_name,quantity,@@spid,'order number and product name must be unique' FROM inserted WHERE EXISTS \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT * FROM dbo.sales_order_line WHERE\u00a0 inserted.product_name = dbo.sales_order_line.product_name AND inserted.sales_order_number = dbo.sales_order_line.sales_order_number\r\n)\r\n\u00a0OR EXISTS (SELECT * FROM inserted sub WHERE inserted.sales_order_number = sub.sales_order_number\r\n\u00a0AND inserted.product_name = sub.product_name\r\n\u00a0AND (inserted.quantity != sub.quantity\r\n\u00a0OR (inserted.quantity IS NULL AND sub.quantity IS NOT NULL) OR (inserted.quantity IS NOT NULL and sub.quantity IS NULL)))\r\nIF @@rowcount > 0 SET @error = 1\r\n\r\nINSERT INTO ##err_dbo_sales_order_line(sales_order_number,product_name,quantity, spid, constraint_name)\r\nSELECT sales_order_number,product_name,quantity,@@spid,'sales order line must belong to valid order' FROM inserted \r\n\u00a0WHERE NOT EXISTS (SELECT * FROM dbo.sales_order WHERE \r\n\u00a0dbo.sales_order.sales_order_number = inserted.sales_order_number)\r\nIF @@rowcount > 0 SET @error = 1\r\nINSERT INTO ##err_dbo_sales_order_line(sales_order_number,product_name,quantity, spid, constraint_name)\r\nSELECT sales_order_number,product_name,quantity,@@spid,'sales order line must be for valid product' FROM inserted \r\n\u00a0WHERE NOT EXISTS (SELECT * FROM dbo.product WHERE \r\n\u00a0dbo.product.product_name = inserted.product_name)\r\nIF @@rowcount > 0 SET @error = 1\r\n\r\nIF @error = 0 \r\nBEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT INTO dbo.sales_order_line\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT DISTINCT * FROM inserted\r\nEND\r\nELSE\r\nBEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAISERROR('Constraint violation(s) - SELECT * FROM\u00a0 ##err_dbo_sales_order_line WHERE spid = @@spid for details', 16, 1)\r\nEND\r\n<\/pre>\ninsert into sales_order_Line \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sales_order_number, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 product_name, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 quantity\r\n)\r\nvalues (100,'Bolt',\u00a0 100),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (100, 'Bolt', 20), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (200, 'Bolt', 1), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (100, 'screw', 4) \r\n<\/pre>\n
Msg 50000, Level 16, State 1, Procedure dbo_sales_order_line_constraint_check_insert, Line 43\r\nConstraint violation(s) - SELECT * FROM\u00a0 ##err_dbo_sales_order_line WHERE spid = @@spid for details\r\nMsg 3609, Level 16, State 1, Line 1\r\nThe transaction ended in the trigger. The batch has been aborted.\r\n<\/pre>\n
SELECT * FROM\u00a0 ##err_dbo_sales_order_line WHERE spid = @@spid<\/pre>\n
sales_order_number product_name quantity spid\u00a0\u00a0\u00a0\u00a0 constraint_name\r\n100\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Bolt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100\u00a0\u00a0\u00a0\u00a0\u00a0 53\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0order number and product name must be unique\r\n100\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Bolt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a020\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a053\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0order number and product name must be unique\r\n200\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Bolt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a053\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0sales order line must belong to valid order\r\n100\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0screw\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 53\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0sales order line must be for valid product\r\n<\/pre>\n
\n
Conclusion<\/h2>\n
References<\/h2>\n
Acknowledgments<\/h2>\n