Hands-On SQL Table & Constraints — Practical Oracle Examples

A friendly, step-by-step walkthrough of creating tables and constraints in Oracle. Includes live examples, expected outputs, and diagrams — perfect practice for the 1Z0-071 exam.

1 Learning objectives

Understand inline vs out-of-line constraint declaration
Create PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY, and CHECK constraints
See how ON DELETE SET NULL and ON DELETE CASCADE behave
Practice reading errors (e.g., ORA-00001, ORA-01400, ORA-02275)

2 Create tables: inline and out-of-line constraints

Below are the exact SQL statements we ran. Use the copy button to paste into your SQL worksheet.

-- Inline primary key, simple columns
CREATE TABLE table_inline(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(20),
    surname VARCHAR2(20)
);

-- Out-of-line primary key (named constraint)
CREATE TABLE table_outline(
    id NUMBER,
    name VARCHAR2(20),
    surname VARCHAR2(20),
    CONSTRAINT pk PRIMARY KEY(id)
);

-- Composite primary key on (name,surname)
CREATE TABLE table_outline_2(
    id NUMBER,
    name VARCHAR2(20),
    surname VARCHAR2(20),
    CONSTRAINT pk_name_surname PRIMARY KEY(name,surname)
);

To check the constraints that we have created you can use the Dictionary

SELECT * FROM user_constraints
WHERE table_name LIKE 'TABLE_%';

SELECT * FROM user_cons_columns
WHERE table_name LIKE 'TABLE_%'
ORDER BY table_name;

3 — ALTER TABLE: what works (and what doesn’t)

Important Oracle gotchas:

One MODIFY per column: you cannot modify multiple columns with a single MODIFY clause separated by commas.
Multiple ADD CONSTRAINT clauses in one statement are not supported in some Oracle versions — best to run separate ALTER TABLE ... ADD CONSTRAINT statements.
NOT NULL is normally defined inline, but you can use ALTER TABLE ... MODIFY column NOT NULL.

-- Modify single column to add NOT NULL
ALTER TABLE table_normal
  MODIFY surname NOT NULL;

-- Add a unique constraint (use separate statements)
ALTER TABLE table_normal
  ADD CONSTRAINT pk_uni1 UNIQUE(name);

ALTER TABLE table_normal
  ADD CONSTRAINT pk_uni2 UNIQUE(surname);

4 — Insert rows and observe constraint behavior

We created table_normal and inserted rows to test uniqueness and NOT NULL behavior.

CREATE TABLE table_normal(
    id NUMBER,
    name VARCHAR2(20),
    surname VARCHAR2(20)
);

-- Make id primary key (example of ALTER ... MODIFY for a single column)
ALTER TABLE table_normal
  MODIFY id PRIMARY KEY;

-- Insert example rows
INSERT INTO table_normal VALUES (1,'PETER','DCT');
INSERT INTO table_normal VALUES (2,'MARK','ZUBER');

Expected result after the two inserts:

ID	NAME	SURNAME
1	PETER	DCT
2	MARK	ZUBER

If we try to insert a record which violate the Primary Key Constraint or the Not Null Constraint we have an error:

-- Trying to insert duplicate id
INSERT INTO table_normal VALUES(1,'MARK','SENT');
-- ORA-00001: unique constraint violated

-- Trying to insert NULL for primary key
INSERT INTO table_normal VALUES(NULL,'ROSS','BRIK');
-- ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE_NORMAL"."ID")

5 — Foreign keys: parent-child relationship and join examples

We create a child table CDEP that references table_normal(id) and test joins and integrity errors.

CREATE TABLE cdep(
  dep VARCHAR2(20),
  emp_id NUMBER,
  CONSTRAINT fk FOREIGN KEY(emp_id) REFERENCES table_normal(id)
);

-- Insert valid rows
INSERT INTO cdep(dep,emp_id) VALUES('ACCOUNTING',1);
INSERT INTO cdep(dep,emp_id) VALUES('MARKETING',2);

Join to show employee details with department:

SELECT t.id, t.name, t.surname, c.dep
FROM table_normal t
JOIN cdep c ON t.id = c.emp_id;

Expected join output:

ID	NAME	SURNAME	DEP
1	PETER	DCT	ACCOUNTING
2	MARK	ZUBER	MARKETING

Attempting to insert a child row that references a non-existent parent (e.g., emp_id = 3) yields ORA-02291: integrity constraint violated.

7 — CASCADE behavior

In this section will look at some example on how to cascade from primary to child table

create table t1(
    id number primary key,
    vehicle varchar2(20)
) 


create table t2(
    name varchar2(20),
    ssn number,
    vehicle_id number,
    constraint fk_111 foreign key(vehicle_id) references t1(id) on delete cascade
) ;

insert into t1 values(1,'Car');
insert into t1 values(2,'Van');
insert into t1 values(3,'Motorbike');
insert into t1 values(4,'Boat');
insert into t2 values('Pietro','001',1);
insert into t2 values('Rachel','002',2);
insert into t2 values('Lucas','003',3);

I run this query :

select * from t1
join t2 on t1.id = t2.vehicle_id

Now we want to truncate table t1 but also cascade the truncate to all child table

TRUNCATE TABLE t1 CASCADE;

Now both tables are empty

Let’s two alternatives that can be used when we delete rows from the parent table:

A) on delete set null

B) on delete cascade

We can specificy on delete set null or on delete cascade in the child tablle

if we use on delete set null the foreign key in the child table is set to null if we delete the primary key

create table t2(
    name varchar2(20),
    ssn number,
    vehicle_id number,
    constraint fk_111 foreign key(vehicle_id) references t1(id) on delete set null

The join query will give us this result

Now let’s see what happens when I run this code

delete from t1 where Vehicle = 'Car'

If we query table t2 now we can see that the first record has the VEHICLE_ID set to null

Now let’s drop the constraint fk_111 and recreate it so that works with on delete cascade

alter table t2
drop constraint fk_111;

alter table t2
add constraint fk_111 foreign key(vehicle_id) references t1(id) on delete cascade;

We will have this dataset:

Now we delete from t1 the record with id = 2. The table t2 now has only 2 records because the deleted row from parent (id=2) has been cascaded also to the child table t2.

8 — CHECK constraints: rules and failing cases

We create a table MYCHECK with several check constraints and test valid and invalid inserts.

CREATE TABLE mycheck(
    id     NUMBER,
    name   VARCHAR2(20) NOT NULL,
    age    NUMBER CONSTRAINT c_age CHECK (age BETWEEN 0 AND 99),
    mydate DATE   CONSTRAINT c_date CHECK (mydate BETWEEN
                 TO_DATE('01-JAN-2000','DD-MON-YYYY') AND TO_DATE('01-JAN-2099','DD-MON-YYYY')),
    flag   VARCHAR2(1) CHECK (flag IN ('Y','N')),
    salary NUMBER CHECK (salary > 500),
    flag_2 VARCHAR2(4) CHECK (flag_2 IN ('XXXX','YYYY'))
);

Insert one record that is ok with the check constraints

INSERT INTO mycheck(id,name,age,mydate,flag,salary,flag_2)
VALUES (1,'PIETRO',30,TO_DATE('15-JAN-2000','DD-MON-YYYY'),'Y',501,'YYYY');

Invalid inserts (examples and expected errors):

-- Date before allowed range
INSERT INTO mycheck(...) VALUES (...,TO_DATE('15-JAN-1999',...));
-- ORA-02290: check constraint violated

-- Invalid flag_2 value
INSERT INTO mycheck(...) VALUES (...,'ZYYY');
-- ORA-02290: check constraint violated

-- Salary not greater than 500
INSERT INTO mycheck(...) VALUES (...,500,'YYYY');
-- ORA-02290: check constraint violated