CREATE TABLE ARTIST
( ID NUMBER,
NAME VARCHAR2(255)
)
INSERT INTO ARTIST (ID, NAME) VALUES (null, null)
COMMIT
SELECT * FROM ARTIST
ID | NAME | - | - |
---|
DROP TABLE ARTIST
NOT NULL Constraint:
CREATE TABLE ARTIST
( ID NUMBER NOT NULL,
NAME VARCHAR2(255) NOT NULL
)
INSERT INTO ARTIST (ID, NAME) VALUES (null, null)
INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')
INSERT INTO ARTIST (ID, NAME) VALUES (2, 'New Ones')
SELECT * FROM ARTIST
ID | NAME | 1 | New Ones | 2 | New Ones |
---|
DELETE FROM ARTIST
We add an UNIQUE CONSTRAINT for NAME:
ALTER TABLE ARTIST
ADD CONSTRAINT ARTIST_NAME_UK
UNIQUE (NAME)
INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')
INSERT INTO ARTIST (ID, NAME) VALUES (2, 'New Ones')
INSERT INTO ARTIST (ID, NAME) VALUES (1, 'Adele')
SELECT * FROM ARTIST
ID | NAME | 1 | New Ones | 1 | Adele |
---|
DELETE FROM ARTIST
The ID is our main key, the PRIMARY KEY:
ALTER TABLE ARTIST
ADD CONSTRAINT ARTIST_PK
PRIMARY KEY (ID)
INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')
INSERT INTO ARTIST (ID, NAME) VALUES (1, 'Adele')
DROP TABLE ARTIST
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
)
INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')
INSERT INTO ARTIST (ID, NAME) VALUES (2, 'Adele')
INSERT INTO ARTIST (ID, NAME) VALUES (3, 'Coldplay')
INSERT INTO ARTIST (ID, NAME) VALUES (4, 'Silent')
COMMIT
CREATE TABLE ALBUM
( ID NUMBER PRIMARY KEY,
ARTIST_ID NUMBER,
NAME VARCHAR2(255) NOT NULL CONSTRAINT ALBUM_NAME_UK UNIQUE
)
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (22, 4, 'Hello')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (42, 2, 'Hello')
DROP TABLE ALBUM
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)
)
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (22, 4, 'Hello')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (42, 2, 'Hello')
SELECT * FROM ALBUM
ID | ARTIST_ID | NAME | 22 | 4 | Hello | 42 | 2 | Hello |
---|
We can drop Constraints:
ALTER TABLE ALBUM DROP CONSTRAINT ALBUM_NAME_UK
... without losing data:
SELECT * FROM ALBUM
ID | ARTIST_ID | NAME | 22 | 4 | Hello | 42 | 2 | Hello |
---|
... and add Constraints to existing tables (with data):
ALTER TABLE ALBUM
ADD CONSTRAINT ALBUM_NAME_UK UNIQUE (ARTIST_ID, NAME)
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (22, 4, 'Hello')
ROLLBACK
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (21, 2, 'James Bond 007: Skyfall')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (31, 3, 'Ghost Stories')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (32, 3, 'A Head Full of Dreams')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (41, 4, 'Without a Sound')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (51, null, 'From the Unknown')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES (61, null, 'The Lost')
COMMIT
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('71', 999, 'Songs from the Wrong')
We add a FOREIGN CONSTRAINT:
ALTER TABLE ALBUM
ADD CONSTRAINT ALBUM_FK
FOREIGN KEY (ARTIST_ID)
REFERENCES ARTIST (ID)
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 |
---|
DELETE FROM ALBUM a WHERE a.ID = 71
Add FOREIGN KEY – take 2:
ALTER TABLE ALBUM
ADD CONSTRAINT ALBUM_FK
FOREIGN KEY (ARTIST_ID)
REFERENCES ARTIST (ID)
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 |
---|
INSERT INTO ALBUM
(ID, ARTIST_ID, NAME)
VALUES (71, 999, 'Songs from the Wrong')
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)
)
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (203, 22, 'Skyfall')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (301, 31, 'Oceans')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (302, 31, 'A Sky Full of Stars')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (303, 32, 'Everglow')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (501, 51, 'Who''s singing?')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES (603, null, 'Song for Nobody')
COMMIT
SELECT * FROM ARTIST
ID | NAME | 1 | New Ones | 2 | Adele | 3 | Coldplay | 4 | Silent |
---|
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 |
---|
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 |
---|
Test of Foreign Key Constraint:
INSERT INTO SONG (ID, ALBUM_ID, NAME)
VALUES (901, 9999999, 'Wrong Key')
Drop a Foreign Key Constraint:
ALTER TABLE SONG DROP CONSTRAINT SONG_NAME_FK
Drop the Primary Key Constraint:
ALTER TABLE ALBUM DROP PRIMARY KEY
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)
)
Add another Check Constraint:
ALTER TABLE PERSON ADD CONSTRAINT PERSON_SEX_CHK CHECK (SEX IN ('m', 'f'))
INSERT INTO PERSON (ID, NAME, AGE, SEX) VALUES (1, 'x', 28, 'm')
INSERT INTO PERSON (ID, NAME, AGE, SEX) VALUES (2, 'Adams', 135, 'f')
INSERT INTO PERSON (ID, NAME, AGE, SEX) VALUES (3, 'Bond', 14, 'x')
INSERT INTO PERSON (ID, NAME, AGE, SEX) VALUES (4, 'Smith', 23, 'f')
SELECT * FROM PERSON
ID | NAME | AGE | SEX | 4 | Smith | 23 | f |
---|