CHECKconstraints are already pretty great when you want to sanitize your data. But there are some limitations to
CHECKconstraints, including the fact that they are applied to the table itself, when sometimes, you want to specify constraints that only apply in certain situations. This can be done with the SQL standard
WITH CHECK OPTIONclause, which is implemented by at least Oracle and SQL Server. Here’s how to do that:
As you can see,
CREATE TABLE books ( id NUMBER(10) NOT NULL, title VARCHAR2(100 CHAR) NOT NULL, price NUMBER(10, 2) NOT NULL, CONSTRAINT pk_book PRIMARY KEY (id) ); / CREATE VIEW expensive_books AS SELECT id, title, price FROM books WHERE price > 100 WITH CHECK OPTION; / INSERT INTO books VALUES (1, '1984', 35.90); INSERT INTO books VALUES ( 2, 'The Answer to Life, the Universe, and Everything', 999.90 );
expensive_booksare all those books whose price is more than 100.00. This view will only report the second book:
The above query yields:
SELECT * FROM expensive_books;
ID TITLE PRICE -- ----------------------------------------- ------- 2 The Answer to Life, the Universe, and ... 999.9But now, that we have that CHECK OPTION, we can also prevent users from inserting “expensive books” that aren’t really expensive. For instance, let’s run this query:
This query won’t work now. We’re getting:
INSERT INTO expensive_books VALUES (3, '10 Reasons why jOOQ is Awesome', 9.99);
ORA-01402: view WITH CHECK OPTION where-clause violationWe also cannot update any of the “expensive books” to be non-expensive:
This query results in the same ORA-01402 error message.
UPDATE expensive_books SET price = 9.99;
Inline WITH CHECK OPTIONIn case you need to locally prevent bogus data from being inserted into a table, you can also use inline
WITH CHECK OPTIONclauses like so:
And the above query again resutls in an ORA-01402 error.
INSERT INTO ( SELECT * FROM expensive_books WHERE price > 1000 WITH CHECK OPTION ) really_expensive_books VALUES (3, 'Modern Enterprise Software', 999.99);
Using SQL transformation to generate ad-hoc constraintsWhile
CHECK OPTIONis very useful for stored views, which can have proper grants for those users that may not access the underlying table directly, the inline
CHECK OPTIONis mainly useful when you transform dynamic SQL in an intermediate SQL transformation layer in your applciation. This can be done with jOOQ’s SQL transformation capabilities, for instance, where you can watch out for a certain table in your SQL statements, and then centrally prevent bogus DML from being executed. This is a great way to implement multi-tenancy, if your database doesn’t natively support row-level security. Stay tuned for a future blog post explaining how to transform your SQL with jOOQ to implement row-level security for any database.