歡迎光臨
每天分享高質量文章

每個開發人員都需要瞭解的一個SQL技巧

(點選上方藍字,快速關註我們)

英文:troyhunt

譯者:花名有孚

網址:http://it.deepinmind.com/sql/2014/09/28/awesome-sql-trick-constraints-on-views.html

點選“閱讀原文”可檢視本文網頁版

對於資料過濾而言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_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);

正如你看到的那樣,expensive_books 是那些價格大於100塊的書。這個檢視只會傳回第二本書:

SELECT * FROM expensive_books;

上述查詢的輸出是:

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

不過由於我們使用了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_booksSET 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_booksVALUES (3, 'Modern Enterprise Software', 999.99);

上述查詢同樣也會導到ORA-01402錯誤。

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

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

這個可以透過jOOQ的SQL轉換功能來完成,比如說,你可以在SQL陳述句中對某個表進行約束,從根本上阻止了非法DML的執行。如果你的資料庫沒有本地提供行級別的安全性的話,這也是一個實現多租戶的不錯的方式。


資料庫開發

微訊號:DBDevs

打造東半球最好的 資料庫 微訊號

——–—————–————-

商務合作QQ:2302462408

招聘和獵頭服務QQ:2302462408

投稿網址:top.jobbole.com



贊(0)

分享創造快樂