转载

每个开发人员都需要了解的一个SQL技巧

对于数据过滤而言CHECK约束已经算是相当不错了。然而它仍存在一些缺陷，比如说它们是应用到表上面的，但有的时候你可能希望指定一条约束，而它只在特定条件下才生效。

每个开发人员都需要了解的一个SQL技巧

使用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 );

正如你看到的那样，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_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错误。