CODEWITHSUNDEEP
sqldatabaseoracle-database
DKG
DKG

Reputation: 387

How to add a DB restriction - Check constraint or trigger

I want to know how can I add a DB restriction on a table. I want to simplify the problem with a table in Oracle Database as

CREATE TABLE TEST_STUDENT
(

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

A student can have any number of subjects but only one of them can be a language (IS_LANG).

Valid data would be

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

however, I should not be able to insert fresh data like up the table, something like

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

or 

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

I don't want to introduce triggers here, unless it is the only way around. I want to have this restrictions because in the actual software would have multiple client implementations trying to insert data into this table.

Upvotes: 2

Views: 121

Answers (1)

user330315
user330315

Reputation:

This one of the good examples for a partial index.

Unfortunately in Oracle you need a workaround to implement a partial index (other DBMS simply allow a WHERE clause to be applied):

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

This exploits the fact that Oracle does not index tuples where all columns are null. With the above expression only rows where IS_LANG = 1 will be indexed for each student. As the index is defined as unique, only one such row can exist.

Here is a SQLFiddle example: http://sqlfiddle.com/#!4/43394d/1

Upvotes: 9

Related Questions