A SQL skill that every programmer needs to understand.

A SQL skill that every programmer needs to understand.

CHECK constraints are already quite good for data filtering. However, it still has some defects. For example, they are applied to the table, but sometimes you may want to specify a constraint, which takes effect only under specific conditions.

This can be done using the standard SQL with check option clause. At least Oracle and SQL Server have implemented this function. The implementation method is as follows:

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_booksASSELECT id, title, priceFROM booksWHERE price > 100WITH 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 books with a price of more than 100 yuan. This view will only return the second book:

SELECT * FROM expensive_books;

The output of the preceding query is:

ID TITLE                                       PRICE-- ----------------------------------------- ------- 2 The Answer to Life, the Universe, and ...   999.9

However, because we use check option, we can also prevent users from inserting cheap ones into "expensive books. For example, run the following query:

INSERT INTO expensive_books VALUES (3, '10 Reasons why jOOQ is Awesome', 9.99);

It cannot take effect. You will see:

ORA-01402: view with check option where-clause violation

We cannot make your books cheaper:

UPDATE expensive_booksSET price = 9.99;

This query also reports the same ORA-01402 error.

With check option inline

If you need to locally 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 > 1000  WITH CHECK OPTION) really_expensive_booksVALUES (3, 'Modern Enterprise Software', 999.99);

The preceding query will also result in a ORA-01402 error.

Use SQL conversion to generate special constraints

Check option is very useful for stored views. It allows users who do not have the right to directly access the underlying table to obtain the correct authorization, the Inline check option mainly converts dynamic SQL statements in the intermediate SQL Conversion Layer of the application.

This can be accomplished through the SQL Conversion Function of jOOQ. For example, you can restrict a table in an SQL statement to fundamentally prevent execution of illegal DML. If your database does not provide row-level security locally, this is also a good way to implement multi-tenant.

Copyright Disclaimer: you are welcome to reprint it. I hope you can add the original article address while reprinting it. Thank you for your cooperation.