Database Tutorial

Course Tutorial Site

Site Admin

Moving Columns

without comments

Learning Outcomes

  • Learn how to move columns from one table to another.

Supporting Materials

The following web pages may help you understand the SQL syntax in this tutorial:

Lesson Materials

There are two key use cases for moving tables. They are:

  • Removing a partial dependency from a table.
  • Removing a transitive dependency from a table.

The difference between the two use cases is the former requires replacing a descriptive element of the natural key while the latter requires replacing a non-key descriptive element and removing the descriptive dependency column. Both replace one column with a foreign key that points to a primary key where the descriptive column or columns is the subject of a new table.

The difference means when you remove a partial dependency you must typically drop the UNIQUE constraint that qualifies the natural key. After you move the partial dependent and functionally dependent column or columns, you must recreate the UNIQUE constraint across any unchanged columns and the new foreign key column.

The following example shows you how to move a transitive dependency into a new table. You first decide on a name for the new table and create a sequence that uses the new table name and an _s suffix.

You start with the following item table:

Name                                       Null?    Type
----------------------------------------- -------- ----------------------------
ITEM_ID                                    NOT NULL NUMBER
ITEM_BARCODE                               NOT NULL VARCHAR2(20)
ITEM_TYPE                                  NOT NULL NUMBER
ITEM_TITLE                                 NOT NULL VARCHAR2(60)
ITEM_SUBTITLE                                       VARCHAR2(60)
ITEM_DESC                                  NOT NULL CLOB
ITEM_PHOTO                                          BINARY FILE LOB
ITEM_RATING                                NOT NULL VARCHAR2(8)
ITEM_RATING_AGENCY                         NOT NULL VARCHAR2(4)
ITEM_RELEASE_DATE                          NOT NULL DATE
CREATED_BY                                 NOT NULL NUMBER
CREATION_DATE                              NOT NULL DATE
LAST_UPDATED_BY                            NOT NULL NUMBER
LAST_UPDATE_DATE                           NOT NULL DATE

The item_rating column has a transitive dependency on the item_rating_agency and both columns should be moved into their own single subject table. You can create that table by coupling the behavior or a CREATE TABLE statement and query.

  1. You create a sequence for the new rating_agency table: 

    SQL> CREATE SEQUENCE rating_agency_s START WITH 1001;

    SQL> CREATE TABLE rating_agency AS
  2    SELECT rating_agency_s.NEXTVAL AS rating_agency_id
  3    ,      il.item_rating AS rating
  4    ,      il.item_rating_agency AS rating_agency
  5    FROM  (SELECT DISTINCT
  6                  i.item_rating
  7           ,      i.item_rating_agency
  8           FROM   item i) il;

    Query the table to see the unique results, which are generated by using the DISTINCT operator in the runtime view of the FROM clause.

    SQL> COLUMN rating_agency_id  FORMAT 9999  HEADING "Rate|ID #"
SQL> COLUMN rating_agency     FORMAT A6    HEADING "Rate|Agency"
SQL> COLUMN rating            FORMAT A8    HEADING "Rate"
SQL> SELECT   rating_agency_id
  2  ,	      rating_agency
  3  ,	      rating
  4  FROM     rating_agency;

    It prints

     Rate Rate
 ID # Agency Rate
----- ------ --------
 1001 MPAA   PG
 1002 ESRB   T
 1003 MPAA   R
 1004 MPAA   NR
 1005 MPAA   PG-13
 1006 ESRB   E10+
 1007 MPAA   G

  2. You need to manually add a PRIMARY KEY constraint because you can’t do that when creating a table from a query. You add a PRIMARY KEY constraint with the following syntax: 

    SQL> ALTER TABLE rating_agency
  2    ADD PRIMARY KEY (rating_agency_id);

  3. You create a rating_agency_id foreign key column in the item table. It must initially be an optional column because there are already rows in the item table. After you add the column and populate it with values, you will add the NOT NULL constraint to the rating_agency_id column.

    You can add the rating_agency column with the following syntax:

    SQL> ALTER TABLE item
  2    ADD (rating_agency_id NUMBER);

    You can populate values with the following UPDATE statement:

    SQL> UPDATE item i
  2  SET    rating_agency_id = (SELECT ra.rating_agency_id
  3                             FROM   rating_agency ra
  4                             WHERE  ra.rating = i.item_rating
  5                             AND    ra.rating_agency = i.item_rating_agency);

    You can now make the rating_agency_id foreign key column mandatory by modifying the column with a NOT NULL constraint. The syntax for that is:

    SQL> ALTER TABLE rating_agency
  2    MODIFY (rating_agency_id  NUMBER  CONSTRAINT nn_rating_agnecy NOT NULL);

  4. You can now drop the columns that you moved from the item table. You use the following syntax to do that. However, you should note that DROP COLUMN clauses can’t be combined with other clauses in an Oracle ALTER TABLE statement. That means you need to write two statements. Each statement drops one column from the original item table. 

    SQL> ALTER TABLE item
  2    DROP COLUMN item_rating;

    and

    SQL> ALTER TABLE item
  2    DROP COLUMN item_rating_agency;

Written by michaelmclaughlin

August 13th, 2018 at 2:42 pm

Posted in