CHECK constraints are already pretty great when you want to sanitize your data. But there are some limitations to CHECK constraints, 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 OPTION clause, which is implemented by at least Oracle and SQL Server. Here’s how to do that:
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 );
As you can see, expensive_books are all those books whose price is more than 100.00. This view will only report the second book:
SELECT * FROM expensive_books;
The above query yields:
ID TITLE PRICE -- ----------------------------------------- ------- 2 The Answer to Life, the Universe, and ... 999.9
But 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:
INSERT INTO expensive_books VALUES (3, '10 Reasons why jOOQ is Awesome', 9.99);
This query won’t work now. We’re getting:
ORA-01402: view WITH CHECK OPTION where-clause violation
We also cannot update any of the “expensive books” to be non-expensive:
UPDATE expensive_books SET price = 9.99;
This query results in the same ORA-01402 error message.
Inline WITH CHECK OPTION
In case you need to locally prevent bogus data from being inserted into a table, you can also use inline WITH CHECK OPTION clauses like so:
INSERT INTO ( SELECT * FROM expensive_books WHERE price > 1000 WITH CHECK OPTION ) really_expensive_books VALUES (3, 'Modern Enterprise Software', 999.99);
And the above query again resutls in an ORA-01402 error.
Using SQL transformation to generate ad-hoc constraints
While CHECK OPTION is very useful for stored views, which can have proper grants for those users that may not access the underlying table directly, the inline CHECK OPTION is 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.
| Reference: | Awesome SQL Trick: Constraints on Views from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
