每個程序員都需要了解的一個SQL技巧

對于數據過濾而言CHECK約束已經算是相當不錯了。然而它仍存在一些缺陷，比如說它們是應用到表上面的，但有的時候你可能希望指定一條約束，而它只在特定條件下才生效。

使用SQL標準的WITH CHECK OPTION子句就能完成這點，至少Oracle和SQL Server都實現了這個功能。下面是實現方式：

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
);</pre> 
正如你看到的那樣，expensive_books 是那些價格大于100塊的書。這個視圖只會返回第二本書： 

SELECT * FROM expensive_books;
 
上述查詢的輸出是： 

ID TITLE                                       PRICE


2 The Answer to Life, the Universe, and ...   999.9</pre> 
不過由于我們使用了CHECK OPTION，我們還能防止用戶往"昂貴的書籍"中插入那些廉價的。比如說，我們運行下這個查詢： 

INSERT INTO expensive_books 
VALUES (3, '10 Reasons why jOOQ is Awesome', 9.99);
 
它是無法生效的。你會看到： 

ORA-01402: view WITH CHECK OPTION where-clause violation 

我們也無法將貴的書更新成便宜的： 

UPDATE expensive_books
SET price = 9.99;
 
這個查詢也會報出同樣的ORA-01402錯誤。 

WITH CHECK OPTION內聯 

如果你需要局部防止臟數據被插入到表中，你可以使用WITH CHECK OPTION的內聯子句： 

INSERT INTO (
  SELECT *
  FROM expensive_books
  WHERE price > 1000
  WITH CHECK OPTION
) really_expensive_books
VALUES (3, 'Modern Enterprise Software', 999.99);
 
上述查詢同樣也會導到ORA-01402錯誤。 

使用SQL轉換來生成特殊約束 

CHECK OPTION對于已存儲的視圖非常有用，它使得那些無權直接訪問底層表的用戶能夠獲得正確的授權，而內聯的CHECK OPTION主要是在應用的SQL中間轉換層來進行動態SQL的轉換。 

這個可以通過jOOQ的SQL轉換功能來完成，比如說，你可以在SQL語句中對某個表進行約束，從根本上阻止了非法DML的執行。如果你的數據庫沒有本地提供行級別的安全性的話，這也是一個實現多租戶的不錯的方式。 
來源： http://it.deepinmind.com/sql/2014/09/28/awesome-sql-trick-constraints-on-views.html