Oracle - DML, Transaction and Constraints

Some examples of my work as a teaching assistant, introducing Data Manipulation, Transaction and Constraints of Oracle Database

Data Manipulation and Transaction Control

Insert Command

When we are trying to add or update data, we always want to take a look at the data and structure of the tables first:

That's what 'AUTHOR' table looks like.

So the insert statement should be:

1.Insert without specifying the column list:

insert into author values ('S201', 'Qi', 'Ren')

Then we can see that it appears in our table.

2.Insert with the column list

We should do like this:

insert into author(authorid, lname, fname) values ('S202', 'Qi', 'Ren')

Null and On Null

In order to test better, we change something in our table:

alter table author add (region varchar(2))

Then the data looks like:

We try to add a new 'null' value into 'author':

insert into author values ('S203', 'Qi', 'Ren', null)

That's it.

Then we try the on null:

First we try:

alter table author modify (region default on null 'ON')

Then we got an error:

Error starting at line : 1 in command -
alter table author modify (region default on null 'ON')
Error report -
ORA-02296: cannot enable (TEMPDB.) - null values found
02296. 00000 - "cannot enable (%s.%s) - null values found"
*Cause:    an alter table enable constraint failed because the table
           contains values that do not satisfy the constraint.

Just as showing, as we already have 'null' values in column 'region', so we cannot do that.

Then we may change it to:

alter table author modify (lname default on null 'unknown')

What will happen if we insert null values into that?

insert into author values('S205', null, 'Qi', null)

We can find the 'null' is changing into 'unknown'.

Now we may want to change our table again:

alter table author add(salary int default 1000, bonus int default 100, total_salary as (salary + bonus))

Then we have a virtual column total_salary!

As we can see, the total_salary is the sum of values in other 2 columns.

If we try to add values into it:

insert into author values ('S206', 'Qi', 'Ren', 'ON', 1100, 100)

It just shows this error message:

Error starting at line : 1 in command -
insert into author values ('S206', 'Qi', 'Ren', 'ON', 1100, 100)
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 -  "not enough values"
*Cause:    
*Action:

Because we missed the total_salary column.

But if we try like this:

insert into author values ('S206', 'Qi', 'Ren', 'ON', 1100, 100, 1200)

It just shows another problem.

Error starting at line : 1 in command -
insert into author values ('S206', 'Qi', 'Ren', 'ON', 1100, 100, 1200)
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-54013: INSERT operation disallowed on virtual columns
54013. 0000 -  "INSERT operation disallowed on virtual columns"
*Cause:    Attempted to insert values into a virtual column
*Action:   Re-issue the statment without providing values for a virtual column

That means we cannot add values into virtual column total_salary

So how do we suppose to do it?

We do like this:

insert into author (authorid, fname, lname, region, salary, bonus) values ('S206', 'Qi', 'Ren', 'ON', 1100, 100)

We can explicitly use those column names, then we are able to add our values.

Inserting Data from an Existing Table

We can select data from existing table and use those data to create a new table.

We first create a table to store the data:

create table author_salary (authorid varchar2(4), salary int, bonus int, salary_total as (salary+ bonus))

Then we add values into it:

insert into author_salary (authorid, salary, bonus) select authorid, salary, bonus from author

Then we can find our data inserted:

Update Command

We can use update to update the existing rows.

update author_salary set salary = '1200' where authorid = 'S100'

Then we can find the salary of 'S100' is updated.

Substitution Variable

We can also let user to input the value we want.

update author_salary set salary = '&Salary' where authorid = '&AuthorId'

We can find that, when we do it like this, we can manually input the value as variables and assign it to rows.

Delete Value

delete from author_salary where authorid = 'S100'

We can find that the row of 'S100' has been deleted.

If we want to delete all the values, we just ignore the where clause and do the deletion:

delete from author_salary

Then all the values in the table was deleted.

Just works like: truncate table author_salary

Transaction Control

So we may re-insert the values again into table author_salary.

insert into author_salary (authorid, salary, bonus) select authorid, salary, bonus from author

The data is like:

and if we do rollback:

rollback; and then we try to find the data:

Then we can find that all the previous inserted values are gone.

also we can try it like this, combine it with savepoint:

insert into author_salary (authorid, salary, bonus) select authorid, salary, bonus from author;
savepoint one;
delete from author_salary;
rollback to one;
select * from author_salary;

We can find that the delete will not work because we rolled back to a previous statement.

But what if we add commit inside?

insert into author_salary (authorid, salary, bonus) select authorid, salary, bonus from author;
savepoint one;
delete from author_salary;
commit;
rollback to one;
select * from author_salary;

it shows error that:

Error starting at line : 5 in command -
rollback to one
Error report -
ORA-01086: savepoint 'ONE' never established in this session or is invalid
01086. 00000 -  "savepoint '%s' never established in this session or is invalid"
*Cause:    An attempt was made to roll back to a savepoint that was never
           established in this session, or was invalid.
*Action:   Try rolling back to the savepoint from the session where it is established.
no rows selected

It means the data was committed and we cannot rollback.

It is important to remember:

DML operations aren’t stored permanently in a table until a COMMIT command is issued implicitly or explicitly

If you want to go further with the Transaction yourself, please check below:

https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT016

Constraints

Add constraints to existing tables

Add primary key to author_salary:

alter table author_salary add constraint pk_author_salary primary key (authorid);

Also, we should check the data first:

When we try to insert a value:

insert into author_salary(authorid, salary, bonus) values ('S100', 1000, 100)

It succeed with "1 row inserted".

But when we try to insert that value again, it shows error that:

Error starting at line : 1 in command -
insert into author_salary(authorid, salary, bonus) values ('S100', 1000, 100)
Error report -
ORA-00001: unique constraint (TEMPDB.PK_AUTHOR_SALARY) violated

It shows that our primary key cannot be duplicated.

Composite primary key

We try like this:

alter table orderitems add constraint pk_order_item primary key (order#, item#);

It shows error that:

Error starting at line : 1 in command -
alter table orderitems add constraint pk_order_item primary key (order#, item#)
Error report -
ORA-02260: table can have only one primary key
02260. 00000 -  "table can have only one primary key"
*Cause:    Self-evident.
*Action:   Remove the extra primary key.

It means there is already a primary key exists, so we should drop it first. We can use drop primary key to drop it.

alter table orderitems drop primary key;

Then we try to add it again:

alter table orderitems add constraint pk_order_item primary key (order#, item#);

Then we can make it.

We can see that we have this:

and if we try to insert value like this:

insert into orderitems values('1020', '1', '3437212490', 1, 19.95)

it shows that

Error starting at line : 1 in command -
insert into orderitems values('1020', '1', '3437212490', 1, 19.95)
Error report -
ORA-00001: unique constraint (TEMPDB.PK_ORDER_ITEM) violated

but if we try to change it a little bit:

insert into orderitems values('1020', '2', '3437212490', 1, 19.95)

We can see it successfully inserted.

Add foreign key constraint

alter table orders add constraint fk_order_customer foreign key (customer#) references customers (customer#);

Same error will happen to show that the constraint already exists. So we still drop it first.

alter table orders drop constraint ORDERS_CUSTOMER#_FK

Then we can manage to add that constraint again.

alter table orders add constraint fk_order_customer foreign key (customer#) references customers (customer#);

and if we try to violate the constraints:

insert into orders values (1021, 9999, '09-04-05', '09-04-05', 'whereever place', 'TORONTO', 'ON', '10000', 2)

it shows errors that:

Error starting at line : 1 in command -
insert into orders values (1021, 9999, '09-04-05', '09-04-05', 'whereever place', 'TORONTO', 'ON', '10000', 2)
Error report -
ORA-02291: integrity constraint (TEMPDB.FK_ORDER_CUSTOMER) violated - parent key not found

That's because we try to add '9999' as customer, and this customer does not exist in customer table.

Unique and not null

unique means no duplication and not null means it cannot be null.

please have a try if you want to do so.

(Also, if you are interested, you can try to add a not null constraint to the column that already contains null values and try to see if it could success.)

Create table with constraints

Here's a sample (auto-generated by Oracle SQL Developer):

Just use the same add constraints methods when you are creating the table.

You can first drop table orderitems and try to add it again.

(Remember in the code, 'TEMPDB' is my own schema name, you may want to change it to yours or just simply delete it before creating your table)

 
  CREATE TABLE "TEMPDB"."ORDERITEMS" 
   (	"ORDER#" NUMBER(4,0), 
	"ITEM#" NUMBER(2,0), 
	"ISBN" VARCHAR2(10 BYTE), 
	"QUANTITY" NUMBER(3,0) NOT NULL ENABLE, 
	"PAIDEACH" NUMBER(5,2) NOT NULL ENABLE, 
	 CONSTRAINT "ODERITEMS_QUANTITY_CK" CHECK (quantity > 0) ENABLE, 
	 CONSTRAINT "PK_ORDER_ITEM" PRIMARY KEY ("ORDER#", "ITEM#")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
	 CONSTRAINT "ORDERITEMS_ORDER#_FK" FOREIGN KEY ("ORDER#")
	  REFERENCES "TEMPDB"."ORDERS" ("ORDER#") ENABLE, 
	 CONSTRAINT "ORDERITEMS_ISBN_FK" FOREIGN KEY ("ISBN")
	  REFERENCES "TEMPDB"."BOOKS" ("ISBN") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

Find keys in tables

Just try to find the constraints from the table we created before:

select constraint_name, constraint_type, search_condition, r_constraint_name
from user_constraints 
where table_name = 'ORDERITEMS'

Here's another way to do so:

select constraint_name, column_name
from user_cons_columns 
where table_name = 'ORDERITEMS'

Disable / Enable Constraints

alter table orderitems disable constraint pk_order_item

Then you may try to add duplicate values into the primary keys, it should work fine.

Drop Constraint

We have already did that before, when we are trying to add foreign keys and primary keys to tables which already have them, we have to drop them first.

By the way, if we want to drop keys that have a reference by other tables:

alter table orders drop primary key

There will be an error:

Error starting at line : 1 in command -
alter table orders drop primary key
Error report -
ORA-02273: this unique/primary key is referenced by some foreign keys
02273. 00000 -  "this unique/primary key is referenced by some foreign keys"
*Cause:    Self-evident.
*Action:   Remove all references to the key before the key is to be dropped.

Also, if you want to drop table orders, that error may happen again.