sql - 如何添加数据库限制 - 检查约束或触发器

Question

我想知道如何在表上添加数据库限制。我想用 Oracle 数据库中的表将问题简化为

CREATE TABLE TEST_STUDENT
(

STUDENT VARCHAR2(30 CHAR),
SUBJECT VARCHAR2(38) ,
IS_LANG NUMBER(1,0)
);

一个学生可以拥有任意数量的科目，但其中只有一门可以是语言 (IS_LANG)。

有效数据是

Insert into TEST_STUDENT (STUDENT,SUBJECT,IS_LANG) values ('John','Math',);
Insert into TEST_STUDENT (STUDENT,SUBJECT,IS_LANG) values ('John','Science',);
Insert into TEST_STUDENT (STUDENT,SUBJECT,IS_LANG) values ('John','French',1);
Insert into TEST_STUDENT (STUDENT,SUBJECT,IS_LANG) values ('Lily','Math',);
Insert into TEST_STUDENT (STUDENT,SUBJECT,IS_LANG) values ('Lily','English',1);

但是，我不应该能够插入新的数据，比如表格，比如

Insert into TEST_STUDENT (STUDENT,SUBJECT,IS_LANG) values ('John','English',1);

或

Insert into TEST_STUDENT (STUDENT,SUBJECT,IS_LANG) values ('Lily','French',1);

我不想在这里介绍触发器，除非它是唯一的解决方法。我想有这个限制，因为在实际的软件中会有多个客户端实现试图将数据插入到这个表中。

Answer 1

这是部分索引的一个很好的例子。

不幸的是，在 Oracle 中，您需要一种变通方法来实现部分索引(其他 DBMS 只允许应用 WHERE 子句):

create unique index idx_one_language 
   on test_student 
      (
          case when is_lang = 1 then student else null end
      );

这利用了 Oracle 不索引所有 列为空的元组这一事实。使用上面的表达式，只有 IS_LANG = 1 的行才会为每个学生编制索引。由于索引被定义为唯一的，因此只能存在一个这样的行。

这是一个 SQLFiddle 示例:http://sqlfiddle.com/#!4/43394d/1