Friday, August 19, 2016

Example on how to use WITH CHECK OPTION in an INSERT statement



The table "ALBUM" has the following columns:
Name         Null     Type                        
------------ -------- --------------------------- 
ALBUM_ID     NOT NULL NUMBER                      
ALBUM_TITLE  NOT NULL VARCHAR2(50 CHAR)           
ARTIST_ID             NUMBER                      
RELEASE_DATE          TIMESTAMP(6) WITH TIME ZONE 
GENRE                 VARCHAR2(30)                
NUM_SOLD              NUMBER                      
COLLECTION            CHAR(1)        

Extract all albums by the band Kiss:
SELECT ARTIST_ID, ALBUM_TITLE
 FROM ALBUM
 WHERE ARTIST_ID = (SELECT ARTIST_ID FROM ARTIST WHERE ARTIST_NAME='Kiss');

 ARTIST_ID ALBUM_TITLE                                      
---------- ------------
        13 Dynasty            

Add another entry to the "ALBUM" table:
SQL> INSERT INTO
  2  (
  3   SELECT album_id, album_title, artist_id,release_date, genre, num_sold
  4   FROM album
  5   WHERE num_sold BETWEEN 10000 AND 50000
  6   AND artist_id=13 WITH CHECK OPTION
  7   )
  8   VALUES( album_seq.nextval, '&album_title', &artist_id, to_date('&release_date','dd.mm.yyyy'), 'HARD ROCK', 20000 );
Enter value for album_title: Love Gun
Enter value for artist_id: 13
Enter value for release_date: 30.06.1977
old   8:  VALUES( album_seq.nextval, '&album_title', &artist_id, to_date('&release_date','dd.mm.yyyy'), 'HARD ROCK', 20000 )
new   8:  VALUES( album_seq.nextval, 'Love Gun', 13, to_date('30.06.1977','dd.mm.yyyy'), 'HARD ROCK', 20000 )

1 row inserted.

COMMIT;
Run the SELECT above once more to confirm the row has been entred:
ARTIST_ID ALBUM_TITLE                                      
---------- -----------------------
        13 Love Gun                             
        13 Dynasty            

Any attempt to enter values that is not included in the subquery would cause an
ORA-01402: view WITH CHECK OPTION where-clause violation
to be raised.
For example, try entering 15 as the artist_id:
Enter value for album_title: Creatures of the Night
Enter value for artist_id: 15
Enter value for release_date: 13.10.1982
old   8:  VALUES( album_seq.nextval, '&album_title', &artist_id, to_date('&release_date','dd.mm.yyyy'), 'HARD ROCK', 20000 )
new   8:  VALUES( album_seq.nextval, 'Creatures of the Night', 15, to_date('13.10.1982','dd.mm.yyyy'), 'HARD ROCK', 20000 )
 VALUES( album_seq.nextval, 'Creatures of the Night', 15, to_date('13.10.1982','dd.mm.yyyy'), 'HARD ROCK', 20000 )
         *
ERROR at line 8:
ORA-01402: view WITH CHECK OPTION where-clause violation

Note that the subquery, when used in an INSERT statement as above, replaces the table name, as well as the column list.

Source: Oracle documentation

No comments:

Post a Comment