Oracle Live SQL - Script: DHBW Datenbanken Unit 1 - 16

Statement 1

CREATE TABLE ARTIST
(  ID    NUMBER,
   NAME  VARCHAR2(255) 
)

Table created.

Statement 2

INSERT INTO ARTIST (ID, NAME) VALUES (null, null)

1 row(s) inserted.

Statement 3

COMMIT

Statement processed.

Statement 4

An empty row makes no sense! Can we prevent that?

SELECT * FROM ARTIST


ID NAME
 -  -

ID	NAME
-	-

Statement 5

DROP TABLE ARTIST

Table dropped.

Statement 6

NOT NULL Constraint:

CREATE TABLE ARTIST 
(  ID    NUMBER         NOT NULL, 
   NAME  VARCHAR2(255)  NOT NULL 
)

Table created.

Statement 7

No more empty rows!

INSERT INTO ARTIST (ID, NAME) VALUES (null, null)

ORA-01400: cannot insert NULL into ("SQL_ITMDTYOTNHHELLAIUVCURZXOH"."ARTIST"."ID") ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-01400

Statement 8

INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')

1 row(s) inserted.

Statement 9

INSERT INTO ARTIST (ID, NAME) VALUES (2, 'New Ones')

1 row(s) inserted.

Statement 10

Unwanted duplicate Artist Name!

SELECT * FROM ARTIST


ID NAME
1 New Ones
2 New Ones

2 rows selected.

ID	NAME
1	New Ones
2	New Ones

Statement 11

DELETE FROM ARTIST

2 row(s) deleted.

Statement 12

We add an UNIQUE CONSTRAINT for NAME:

ALTER TABLE ARTIST 
  ADD CONSTRAINT ARTIST_NAME_UK 
  UNIQUE (NAME)

Table altered.

Statement 13

INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')

1 row(s) inserted.

Statement 14

No more duplicate Artists!

INSERT INTO ARTIST (ID, NAME) VALUES (2, 'New Ones')

ORA-00001: unique constraint (SQL_ITMDTYOTNHHELLAIUVCURZXOH.ARTIST_NAME_UK) violated ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-00001

Statement 15

INSERT INTO ARTIST (ID, NAME) VALUES (1, 'Adele')

1 row(s) inserted.

Statement 16

Now we have ID = 1 twice!

SELECT * FROM ARTIST


ID NAME
1 New Ones
1 Adele

2 rows selected.

ID	NAME
1	New Ones
1	Adele

Statement 17

DELETE FROM ARTIST

2 row(s) deleted.

Statement 18

A PRIMARY KEY is always unique and not null!

The ID is our main key, the PRIMARY KEY:

ALTER TABLE ARTIST 
  ADD CONSTRAINT ARTIST_PK 
  PRIMARY KEY (ID)

Table altered.

Statement 19

INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')

1 row(s) inserted.

Statement 20

It works!

INSERT INTO ARTIST (ID, NAME) VALUES (1, 'Adele')

ORA-00001: unique constraint (SQL_ITMDTYOTNHHELLAIUVCURZXOH.ARTIST_PK) violated ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-00001

Statement 21

DROP TABLE ARTIST

Table dropped.

Statement 22

Constraints can be created together with the table:

CREATE TABLE ARTIST 
(  ID    NUMBER         CONSTRAINT ARTIST_PK PRIMARY KEY, 
   NAME  VARCHAR2(255)  NOT NULL CONSTRAINT ARTIST_NAME_UK UNIQUE 
)

Table created.

Statement 23

INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')

1 row(s) inserted.

Statement 24

INSERT INTO ARTIST (ID, NAME) VALUES (2, 'Adele')

1 row(s) inserted.

Statement 25

INSERT INTO ARTIST (ID, NAME) VALUES (3, 'Coldplay')

1 row(s) inserted.

Statement 26

INSERT INTO ARTIST (ID, NAME) VALUES (4, 'Silent')

1 row(s) inserted.

Statement 27

COMMIT

Statement processed.

Statement 28

CREATE TABLE ALBUM 
(  ID          NUMBER         PRIMARY KEY, 
   ARTIST_ID   NUMBER, 
   NAME        VARCHAR2(255)  NOT NULL   CONSTRAINT ALBUM_NAME_UK UNIQUE 
)

Table created.

Statement 29

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (22,  4, 'Hello')

1 row(s) inserted.

Statement 30

This Album name is rejected, but both Artists have one with this name! What can we do?

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (42,  2, 'Hello')

ORA-00001: unique constraint (SQL_ITMDTYOTNHHELLAIUVCURZXOH.ALBUM_NAME_UK) violated ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-00001

Statement 31

DROP TABLE ALBUM

Table dropped.

Statement 32

Two column UNIQUE CONSTRAINT:

CREATE TABLE ALBUM 
(  ID          NUMBER         PRIMARY KEY, 
   ARTIST_ID   NUMBER, 
   NAME        VARCHAR2(255)  NOT NULL, 
   CONSTRAINT ALBUM_NAME_UK UNIQUE (ARTIST_ID, NAME) 
)

Table created.

Statement 33

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (22,  4, 'Hello')

1 row(s) inserted.

Statement 34

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (42,  2, 'Hello')

1 row(s) inserted.

Statement 35

Fixed!

SELECT * FROM ALBUM


ID ARTIST_ID NAME
22 4 Hello
42 2 Hello

2 rows selected.

ID	ARTIST_ID	NAME
22	4	Hello
42	2	Hello

Statement 36

We can drop Constraints:

ALTER TABLE ALBUM DROP CONSTRAINT ALBUM_NAME_UK

Table altered.

Statement 37

... without losing data:

SELECT * FROM ALBUM


ID ARTIST_ID NAME
22 4 Hello
42 2 Hello

2 rows selected.

ID	ARTIST_ID	NAME
22	4	Hello
42	2	Hello

Statement 38

Find out what happens, when you a constraint to a table with data, that violates the constraint!

... and add Constraints to existing tables (with data):

ALTER TABLE ALBUM 
  ADD CONSTRAINT ALBUM_NAME_UK UNIQUE (ARTIST_ID, NAME)

Table altered.

Statement 39

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (22,  4, 'Hello')

ORA-00001: unique constraint (SQL_ITMDTYOTNHHELLAIUVCURZXOH.SYS_C00737536) violated ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-00001

Statement 40

ROLLBACK

Statement processed.

Statement 41

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (21,  2, 'James Bond 007: Skyfall')

1 row(s) inserted.

Statement 42

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (31,  3, 'Ghost Stories')

1 row(s) inserted.

Statement 43

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (32,  3, 'A Head Full of Dreams')

1 row(s) inserted.

Statement 44

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (41,  4, 'Without a Sound')

1 row(s) inserted.

Statement 45

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (51,  null, 'From the Unknown')

1 row(s) inserted.

Statement 46

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (61,  null, 'The Lost')

1 row(s) inserted.

Statement 47

COMMIT

Statement processed.

Statement 48

We can add song with an ARTIST_ID that does not exists! Not so good!

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('71',  999, 'Songs from the Wrong')

1 row(s) inserted.

Statement 49

Does not work! The existing data violates the constraint!

We add a FOREIGN CONSTRAINT:

ALTER TABLE ALBUM 
  ADD CONSTRAINT ALBUM_FK  
  FOREIGN KEY (ARTIST_ID) 
  REFERENCES ARTIST (ID)

ORA-02298: cannot validate (SQL_ITMDTYOTNHHELLAIUVCURZXOH.ALBUM_FK) - parent keys not found

More Details: https://docs.oracle.com/error-help/db/ora-02298

Statement 50

SELECT * FROM ALBUM


ID ARTIST_ID NAME
22 4 Hello
42 2 Hello
21 2 James Bond 007: Skyfall
31 3 Ghost Stories
32 3 A Head Full of Dreams
41 4 Without a Sound
51  - From the Unknown
61  - The Lost
71 999 Songs from the Wrong

9 rows selected.

ID	ARTIST_ID	NAME
22	4	Hello
42	2	Hello
21	2	James Bond 007: Skyfall
31	3	Ghost Stories
32	3	A Head Full of Dreams
41	4	Without a Sound
51	-	From the Unknown
61	-	The Lost
71	999	Songs from the Wrong

Statement 51

We drop the bad row

DELETE FROM ALBUM a WHERE a.ID = 71

1 row(s) deleted.

Statement 52

Add FOREIGN KEY – take 2:

ALTER TABLE ALBUM 
  ADD CONSTRAINT ALBUM_FK  
  FOREIGN KEY (ARTIST_ID) 
  REFERENCES ARTIST (ID)

Table altered.

Statement 53

SELECT * FROM ALBUM


ID ARTIST_ID NAME
22 4 Hello
42 2 Hello
21 2 James Bond 007: Skyfall
31 3 Ghost Stories
32 3 A Head Full of Dreams
41 4 Without a Sound
51  - From the Unknown
61  - The Lost

8 rows selected.

ID	ARTIST_ID	NAME
22	4	Hello
42	2	Hello
21	2	James Bond 007: Skyfall
31	3	Ghost Stories
32	3	A Head Full of Dreams
41	4	Without a Sound
51	-	From the Unknown
61	-	The Lost

Statement 54

No we are protected from wrong Artist ID's:

INSERT INTO ALBUM 
       (ID, ARTIST_ID, NAME)  
VALUES (71, 999,       'Songs from the Wrong')

ORA-02291: integrity constraint (SQL_ITMDTYOTNHHELLAIUVCURZXOH.ALBUM_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-02291

Statement 55

Table SONG with PRIMARY KEY, NOT NULL, UNIQUE Constraint and FOREIGN KEY

CREATE TABLE SONG 
(  ID          NUMBER         CONSTRAINT SONG_PK PRIMARY KEY, 
   ALBUM_ID    NUMBER, 
   NAME        VARCHAR2(255)  NOT NULL, 
   CONSTRAINT SONG_NAME_UK UNIQUE (ALBUM_ID, NAME), 
   CONSTRAINT SONG_NAME_FK FOREIGN KEY (ALBUM_ID) REFERENCES ALBUM (ID) 
)

Table created.

Statement 56

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (203, 22, 'Skyfall')

1 row(s) inserted.

Statement 57

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (301, 31, 'Oceans')

1 row(s) inserted.

Statement 58

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (302, 31, 'A Sky Full of Stars')

1 row(s) inserted.

Statement 59

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (303, 32, 'Everglow')

1 row(s) inserted.

Statement 60

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (501, 51, 'Who''s singing?')

1 row(s) inserted.

Statement 61

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (603, null, 'Song for Nobody')

1 row(s) inserted.

Statement 62

COMMIT

Statement processed.

Statement 63

SELECT * FROM ARTIST


ID NAME
1 New Ones
2 Adele
3 Coldplay
4 Silent

4 rows selected.

ID	NAME
1	New Ones
2	Adele
3	Coldplay
4	Silent

Statement 64

SELECT * FROM ALBUM


ID ARTIST_ID NAME
22 4 Hello
42 2 Hello
21 2 James Bond 007: Skyfall
31 3 Ghost Stories
32 3 A Head Full of Dreams
41 4 Without a Sound
51  - From the Unknown
61  - The Lost

8 rows selected.

ID	ARTIST_ID	NAME
22	4	Hello
42	2	Hello
21	2	James Bond 007: Skyfall
31	3	Ghost Stories
32	3	A Head Full of Dreams
41	4	Without a Sound
51	-	From the Unknown
61	-	The Lost

Statement 65

SELECT * FROM SONG


ID ALBUM_ID NAME
203 22 Skyfall
301 31 Oceans
302 31 A Sky Full of Stars
303 32 Everglow
501 51 Who's singing?
603  - Song for Nobody

6 rows selected.

ID	ALBUM_ID	NAME
203	22	Skyfall
301	31	Oceans
302	31	A Sky Full of Stars
303	32	Everglow
501	51	Who's singing?
603	-	Song for Nobody

Statement 66

Foreign Key Constraint permits ALBUM_ID not to be found in table ALBUM!

Test of Foreign Key Constraint:

INSERT INTO SONG (ID,  ALBUM_ID, NAME)  
          VALUES (901, 9999999,  'Wrong Key')

ORA-02291: integrity constraint (SQL_ITMDTYOTNHHELLAIUVCURZXOH.SONG_NAME_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-02291

Statement 67

Drop a Foreign Key Constraint:

ALTER TABLE SONG DROP CONSTRAINT SONG_NAME_FK

Table altered.

Statement 68

No name required - there is only one Primary Key per table!

Drop the Primary Key Constraint:

ALTER TABLE ALBUM DROP PRIMARY KEY

Table altered.

Statement 69

Table with Check Constraints:

CREATE TABLE PERSON (
    ID    NUMBER        CONSTRAINT PERSON_PK PRIMARY KEY,
    NAME  VARCHAR2(255) CONSTRAINT PERSON_NAME_LENGTH_CHK   CHECK (length(NAME) > 2),
    AGE   NUMBER(3)     CONSTRAINT PERSON_AGE_CHK           CHECK (AGE BETWEEN 0 AND 125),
    SEX   VARCHAR2(1)          
)

Table created.

Statement 70

Add another Check Constraint:

ALTER TABLE PERSON ADD CONSTRAINT PERSON_SEX_CHK  CHECK (SEX IN ('m', 'f'))

Table altered.

Statement 71

Check Constraint violated!

INSERT INTO PERSON (ID, NAME, AGE, SEX) VALUES (1, 'x', 28, 'm')

ORA-02290: check constraint (SQL_ITMDTYOTNHHELLAIUVCURZXOH.PERSON_NAME_LENGTH_CHK) violated ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-02290

Statement 72

Check Constraint violated!

INSERT INTO PERSON (ID, NAME, AGE, SEX) VALUES (2, 'Adams', 135, 'f')

ORA-02290: check constraint (SQL_ITMDTYOTNHHELLAIUVCURZXOH.PERSON_AGE_CHK) violated ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-02290

Statement 73

Check Constraint violated!

INSERT INTO PERSON (ID, NAME, AGE, SEX) VALUES (3, 'Bond', 14, 'x')

ORA-02290: check constraint (SQL_ITMDTYOTNHHELLAIUVCURZXOH.PERSON_SEX_CHK) violated ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-02290

Statement 74

INSERT INTO PERSON (ID, NAME, AGE, SEX) VALUES (4, 'Smith', 23, 'f')

1 row(s) inserted.

Statement 75

SELECT * FROM PERSON


ID NAME AGE SEX
4 Smith 23 f

DHBW Datenbanken Unit 1 - 16 - Constraints

Additional Information