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 violationto 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