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