A SQL technique that every programmer needs to understand

Check constraints are pretty good for data filtering. However, it still has some drawbacks, such as that they are applied to the table, but sometimes you might want to specify a constraint that only takes effect under certain conditions.

This can be done using the SQL standard with CHECK option clause, at least both Oracle and SQL Server implement this functionality. Here's how it's implemented:

CREATE TABLE Books (  ID number    ) not         null,  title VARCHAR2 (+ CHAR) not NULL, price number  (10, 2) C5/>not NULL,  CONSTRAINT pk_book PRIMARY KEY (ID)),/create VIEW expensive_booksasselect ID, title, Pricefrom bookswhe  RE price > 100WITH CHECK option;/insert to 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 is a book that costs more than 100 dollars. This view will return only the second book:

SELECT * from Expensive_books;

The output of the above query is:

ID TITLE Price--------------------------------------------------2 of the Answer to life                                       , the Universe, and ...   999.9

However, because we use check OPTION, we can also prevent users from inserting cheap books into the "expensive book". For example, let's run the following query:

INSERT into Expensive_books VALUES (3, ' Reasons why Jooq is Awesome ', 9.99);

It is not valid. You will see:

Ora-01402:view with CHECK OPTION where-clause violation

We are also unable to update your book to a cheaper price:

UPDATE Expensive_booksset price = 9.99;

This query will also report the same ORA-01402 error.

With CHECK option inline

If you need to partially prevent dirty data from being inserted into the table, you can use the inline clause with CHECK option:

INSERT into (  SELECT * from  expensive_books  WHERE price > all with  CHECK OPTION) really_expensive_ Booksvalues (3, ' Modern Enterprise software ', 999.99);

The same query will also lead to ORA-01402 errors.

Using SQL transforms to generate special constraints

Check option is useful for stored views, which enable users who do not have direct access to the underlying tables to obtain the correct authorization, whereas inline check option is used primarily for dynamic SQL conversions at the application's SQL Intermediate transformation layer.

This can be done through the Jooq SQL conversion function, for example, you can constrain a table in an SQL statement, fundamentally preventing the execution of illegal DML. This is also a good way to implement multi-tenancy if your database does not provide row-level security locally.

Copyright NOTICE: Welcome reprint, Hope in your reprint at the same time, add the original address, thank you with

A SQL technique that every programmer needs to understand