DHBW Datenbanken Unit 1 - 16 - Constraints

View All Scripts Login to Run Script

Script Name DHBW Datenbanken Unit 1 - 16 - Constraints
Description DHBW Datenbanken Unit 1
Area SQL General
Contributor Matthias K. F. Schulz
Created Tuesday October 25, 2016

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
- -
Statement 5
```
DROP TABLE ARTIST
```
Table dropped.

ID	NAME
-	-

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

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.
Statement 11
```
DELETE FROM ARTIST
```
2 row(s) deleted.

ID	NAME
1	New Ones
2	New Ones

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

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

ID	NAME
1	New Ones
1	Adele

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
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
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.
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.
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
Statement 40
```
ROLLBACK
```
Statement processed.

ID	ARTIST_ID	NAME
22	4	Hello
42	2	Hello

ID	ARTIST_ID	NAME
22	4	Hello
42	2	Hello

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
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.
Statement 51
We drop the bad row
```
DELETE FROM ALBUM a WHERE a.ID = 71
```
1 row(s) deleted.

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

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

ID	NAME
1	New Ones
2	Adele
3	Coldplay
4	Silent

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

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

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

ID	NAME	AGE	SEX
4	Smith	23	f

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials