Create a Table (PATRONS)

The PATRONS table contains a row for each patron who can check books out of the library (that is, each person who has a library card). It includes an object type (MDSYS.SDO_GEOMETRY) column. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:

CREATE TABLE patrons (
   patron_id NUMBER,
   last_name VARCHAR2(30)
      CONSTRAINT patron_last_not_null NOT NULL,
   first_name VARCHAR2(30),
   street_address VARCHAR2(50),
   city_state_zip VARCHAR2(50),
   location MDSYS.SDO_GEOMETRY,
   CONSTRAINT patrons_pk PRIMARY KEY (patron_id));

The use of single city_state_zip column for all that information is not good database design; it is done here merely to simplify your work in the tutorial.

The location column (Oracle Spatial and Graph geometry representing the patron's geocoded address) is merely to show the use of a complex (object) type.

To create the PATRONS table, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it. Be sure that the Advanced box is not checked when you start creating the table.)

Schema: Specify your current schema as the schema in which to create the table.

Name: PATRONS

Create most of the table columns using the following information. After creating each column except the city_state_zip column, click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the PATRONS table in the Connections navigator display, select Edit, and continue to add columns.)

Column Name Type Size Other Information and Notes
patron_id NUMBER
Primary Key. (Unique patron ID number, with values to be created using a sequence that you will create)
last_name VARCHAR2 30 Not Null
first_name VARCHAR2 30
street_address VARCHAR2 30
city_state_zip VARCHAR2 30

The last column in the table (location) requires a complex data type, for which you must use the Columns tab with advanced options. Check Advanced (next to Schema). This displays a pane for selecting more table options.

In the Columns pane, click the city_state_zip column name, and click the Add Column (+) icon to add the following as the last column in the table.

Column Name Type Other Information and Notes
location Complex type

Schema: MDSYS

Type: SDO_GEOMETRY

(Oracle Spatial and Graph geometry object representing the patron's geocoded address)

After you have entered the last column (location), click OK to finish creating the table.

Go to Create a Table (TRANSACTIONS) to create the next table.

Related Topics

SQL Developer Tutorial: Creating Objects for a Small Database

SQL Developer User Interface