X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • December 17, 2015

Creating Multiple Tables in a Single Transaction

Chris Saxon
Developer Advocate

The following quiz is taken from the Oracle Dev Gym. Can you guess which choices are correct?

The Question

Your boss has asked you to create a release script for the tables shown in this schema diagram:

 

You should create these tables in the PLCH_APP_OWNER user.

Which of the following choices will create the tables shown above?

You can assume that you are connected to the database as the PLCH_APP_OWNER user for this question.

The Choices

Choice 1

create table plch_app_owner.plch_orders (
    order_id    integer not null ,
    order_date  date not null ,
    customer_id integer not null
) ;

alter table plch_app_owner.plch_orders add constraint 
  plch_orders_pk primary key ( order_id ) ;

create table plch_app_owner.plch_order_items (
    order_id    integer not null ,
    item_number integer not null ,
    product_id  integer not null ,
    quantity    integer not null ,
    unit_price  number (10,2) not null
) ;

alter table plch_app_owner.plch_order_items add constraint 
  plch_order_items_pk primary key ( order_id, item_number ) ;
  
alter table plch_app_owner.plch_order_items add constraint 
  plch_orit_plch_orders_fk foreign key ( order_id ) 
  references plch_orders ( order_id ) ;

Choice 2

create schema authorization plch_app_owner
  create table plch_orders (
    order_id    integer not null ,
    order_date  date not null ,
    customer_id integer not null,
    constraint plch_orders_pk primary key ( order_id ) 
) 
  create table plch_order_items (
    order_id    integer not null ,
    item_number integer not null ,
    product_id  integer not null ,
    quantity    integer not null ,
    unit_price  number (10,2) not null,
    constraint plch_order_items_pk 
      primary key ( order_id, item_number ) ,
    constraint plch_orit_plch_orders_fk 
      foreign key ( order_id ) 
      references plch_orders ( order_id ) 
) ;

Choice 3

create schema 
  create table plch_app_owner.plch_orders (
    order_id    integer not null ,
    order_date  date not null ,
    customer_id integer not null,
    constraint plch_orders_pk primary key ( order_id ) 
) 
  create table plch_app_owner.plch_order_items (
    order_id    integer not null ,
    item_number integer not null ,
    product_id  integer not null ,
    quantity    integer not null ,
    unit_price  number (10,2) not null,
    constraint plch_order_items_pk 
      primary key ( order_id, item_number ) ,
    constraint plch_orit_plch_orders_fk 
      foreign key ( order_id ) 
      references plch_orders ( order_id ) 
) ;

Choice 4

create schema authorization plch_app_owner
  create table plch_orders (
    order_id    integer not null ,
    order_date  date not null ,
    customer_id integer not null,
    constraint plch_orders_pk primary key ( order_id ) 
) ;

create schema authorization plch_app_owner
  create table plch_order_items (
    order_id    integer not null ,
    item_number integer not null ,
    product_id  integer not null ,
    quantity    integer not null ,
    unit_price  number (10,2) not null,
    constraint plch_order_items_pk 
      primary key ( order_id, item_number ) ,
    constraint plch_orit_plch_orders_fk 
      foreign key ( order_id ) 
      references plch_orders ( order_id ) 
) ;

Choice 5

create schema authorization plch_app_owner
  create table plch_orders (
      order_id    integer not null ,
      order_date  date not null ,
      customer_id integer not null
) 
  alter table plch_orders add constraint 
    plch_orders_pk primary key ( order_id ) 
  create table plch_order_items (
      order_id    integer not null ,
      item_number integer not null ,
      product_id  integer not null ,
      quantity    integer not null ,
      unit_price  number (10,2) not null
) 
  alter table plch_order_items add constraint 
    plch_order_items_pk primary key ( order_id, item_number ) 
  alter table plch_order_items add constraint 
    plch_orit_plch_orders_fk foreign key ( order_id ) 
    references plch_orders ( order_id ) ;

Answer

Oracle issues an implicit commit before and after every DDL statement. Consequently you can't undo DDL by issuing a rollback command. If you have more than one DDL command in a process (e.g. a release script) and one fails, then you must explicitly undo the completed steps to rollback the entire process.

Using the "CREATE SCHEMA" command you can combine multiple DDL steps into a single statement. As a result, the failure of a single part causes all commands within the "CREATE SCHEMA" to be rolled back.

CREATE SCHEMA is limited to creating tables, views and issuing grants. If you wish to alter tables, "create or replace" a view or create other objects (e.g. indexes) these must be issued as separate statements. If you want to create constraints on tables within a "create schema", you must use inline constraints.

Oracle will automatically resolve foreign key dependencies within a create schema command. This means it doesn't matter which order the tables appear in the command. Provided the end result of the schema creation is consistent, all the tables will be created. For example, choice 2 could be rewritten as:

create schema authorization plch_app_owner
  create table plch_order_items (
    order_id    integer not null ,
    item_number integer not null ,
    product_id  integer not null ,
    quantity    integer not null ,
    unit_price  number (10,2),
    constraint plch_order_items_pk 
      primary key ( order_id, item_number ) ,
    constraint plch_orit_plch_orders_fk 
      foreign key ( order_id ) 
      references plch_orders ( order_id ) 
) 
  create table plch_orders (
    order_id    integer not null ,
    order_date  date not null ,
    customer_id integer not null,
    constraint plch_orders_pk primary key ( order_id ) 
) ;

The command creates the tables, despite the child table (PLCH_ORDER_ITEMS) being listed first. Finally, you must be connected to the user listed in the AUTHORIZATION clause for CREATE SCHEMA to work. This doesn't create the database user - this must already exist.


Not sure which choices are correct? Click here to take the quiz on the Oracle Dev gym and find out!

How did you get on?

Do you like quizzes such as this?

If so, then head over the Oracle Dev Gym to get your fill of quizzes on SQL, PL/SQL and Database Design. There's new quizzes every week for you to test your skills and learn!

UPDATED: 19 Dec 2017 changed links to point to Oracle Dev Gym instead of PL/SQL Challenge

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.