Oracle:DDL Create

Main Page >> Oracle and SQL >> Workbook >> DDL >> Create

Creating a Table

The code that produced the sample database can be seen in the appendix: Oracle Setup Code

The following command illustrates how the sample DEPT table was created. DO NOT TYPE THIS COMMAND IN!

CREATE TABLE DEPT (
   DEPTNO NUMBER(2) constraint PK_DEPT primary key,
   DNAME	 VARCHAR2(14) NOT NULL 	constraint UC_DNAME check (DNAME=upper(DNAME)),
   LOC	 VARCHAR2(13) constraint UC_LOC check (LOC=upper(LOC)) 
);

The figure in brackets after the data type indicates the length.

The following constraints have been implemented on the Department table to ensure that the integrity of the database is maintained:

• The department number attribute DEPTNO is the primary key because of the constraint PK_DEPT. This also means that the user must insert a value into this attribute when inserting or updating information in this table.
• The department name attribute DNAME must be entered because of the NOT NULL clause and it must be in uppercase because of the constraint UC_DNAME.
• The location attribute LOC may be left blank because of the absence of a NOT NULL clause. If it is entered it must be in uppercase because of the constraint UC_LOC.

The constraint names (e.g. PK_DEPT) are specified so that Oracle can use these as labels for the constraint in its Data Dictionary.

The basic valid data types are:

Type	Precision	Scale	Description
CHAR	n	-	Fixed character of width n
DATE	-	-	Date
NUMBER	n	d	Numeric of width n with d decimal places
VARCHAR2	n	-	Variable character of width n

The following command creates a table called PROJ with three columns PROJNO, PNAME and BUDGET. You should type this in:

CREATE TABLE PROJ (
   PROJNO NUMBER(5) constraint PK_PROJ primary key,
   PNAME VARCHAR2(32) constraint UC_PNAME check (PNAME=upper(PNAME)),
   BUDGET NUMBER(8,2) NOT NULL
   constraint BD_BUDGET check (BUDGET >= 0)
 );

The following constraints have been implemented on the project table PROJ:

• PROJNO is the primary key and is a number because we will use a sequence to generate the next number automatically when a new record is inserted.
• BUDGET cannot have a null value.
• PNAME is a string of 32 characters.
• BUDGET is a number of maximum width 8 characters and with two decimal places.

Composite Primary and Foreign Keys

The primary key (PK) for tables with composite primary keys are defined slightly different from above, the clause PRIMARY KEY is not put after each field taking part in the PK, but as a separate clause. Sample code could be (you do not need to type this in):

CREATE TABLE Tablename1 (
  col1 VARCHAR2(10), col2 VARCHAR2(10), col3 VARCHAR2(3), 
  CONSTRAINT PK_TAB PRIMARY KEY (col1,col2)
 );

Single foreign keys (FK) can be defined using the REFERENCES clause (see the definition for the EMP table in Appendix A for an example).

Composite foreign keys can be defined using the FOREIGN KEY clause. Sample code could be (you do not need to type this in):

 CREATE TABLE Tablename2 (
  col1 VARCHAR2 (10), col2  VARCHAR2 (10), ......, 
  CONSTRAINT FK_TAB2 FOREIGN KEY (col1,col2) REFERENCES Tablename1);

This ensures that any values put into the col1 and col2 columns exist in the related columns of Tablename1 so enforcing referential integrity.

If the column names are not the same in both tables, then the name of the related fields must also be specified:

 CREATE TABLE Tablename3(newcol1 VARCHAR2 (10), newcol2  VARCHAR2 (10), ......, 
   CONSTRAINT FK_TAB3 FOREIGN KEY (newcol1,newcol2) 
   REFERENCES Tablename1 (col1,col2));

For example, to create a table EMPPROJ that records which projects an employee works on:

CREATE TABLE EMPPROJ (
    EMPNO NUMBER(4) constraint fk_emp REFERENCES EMP,
    PROJNO NUMBER(5) constraint fk_proj REFERENCES PROJ,
    HOURS NUMBER(4),
    CONSTRAINT pk_empproj PRIMARY KEY(empno, projno)
 );

To create a further table that keeps a history of an employee’s expenses per project:

CREATE TABLE EMPPROJ_EXPENSES (
   EMPNO NUMBER(4),
   PROJNO NUMBER(5),
   CLAIMDATE DATE,
   CLAIMTOTAL NUMBER(7,2),
   CONSTRAINT pk_ep_exp PRIMARY KEY(empno, projno, claimdate),
   CONSTRAINT fk_ep FOREIGN KEY(empno, projno) REFERENCES EMPPROJ
 );

Creating a Sequence for a Table's Primary Key

The PROJNO attribute is the primary key for the PROJ table. Whenever a new row is inserted into this table a new unique number needs to be allocated. Oracle allows you to do this automatically if you create a sequence for a TABLE.

To do this first create the sequence:

CREATE SEQUENCE PROJSEQ;

Now it can be used instead of a primary key value:

INSERT INTO PROJ VALUES(PROJSEQ.NEXTVAL,'ORACLE VERSION 10',15000);
COMMIT;

The sequence can also be used to add a record to a related table, such as the EMPPROJ table, so long as you add the employees to a particular project at the same time. For example, to add a new project and an employee at the same time:

INSERT INTO PROJ VALUES (PROJSEQ.NEXTVAL,'ORACLE DEVELOPER',10000);
INSERT INTO EMPPROJ VALUES (7902, PROJSEQ.CURRVAL, 20);
COMMIT;

Note, the above 2 inserts form part of a transaction, so the commit is only needed after the last insert.

NEXTVAL returns the next available number, incremented automatically, whereas CURRVAL returns the current value.

Exercise 3.6

3.6.1 Produce SQL statements to list all of the information in the PROJ and EMPPROJ tables.

    PROJNO PNAME                                BUDGET  MGRPROJNO
---------- -------------------------------- ---------- ----------
         1 ORACLE VERSION 10                     15000       7698
         2 ORACLE DEVELOPER                      10000       7566

     EMPNO     PROJNO      HOURS
---------- ---------- ----------
      7902          2         20

3.6.2 As part of the same transaction, create a new project called ORACLE VERSION 12C with a budget of 8000 and the manager is employee number 7782. Insert 2 other existing employees to the EMPPROJ table for this new project and then add some expenses to the EMPPROJ_EXPENSES table for one of these employees. Try to insert the data using the CURRVAL and NEXTVAL attributes and add appropriate data for the other fields.

Identity Columns

Since version 12c Oracle has introduce the concept of an identity column, which is similar to the [| auto increment] columns found in other DBMSs.

For example:

CREATE TABLE identity_test (
  id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
  name VARCHAR2(30)
 );

This creates a table called identity_test and by default if you do not specify a primary key value when inserting data, it will automatically use the next number available (starting at 1).

When inserting data it means you will have to specify the columns that you want to add data to. For example:

INSERT INTO identity_test (name) VALUES ('TEST ROW1');

Check the value added:

SELECT * FROM identity_test;

Using the syntax GENERATED BY DEFAULT does mean you can also add your own ID value:

INSERT INTO identity_test VALUES (2, 'TEST ROW2');

Take care with doing this, since if you then type in:

INSERT INTO identity_test (Name) VALUES ('TEST ROW2');

You will end up with two rows with an identity of 2!

These changes can therefore be lost:

ROLLBACK;

Really if you are going to use an identity column, then you should leave the system to add the unique values. Do note, adding an identity column does not make the column a primary key, which is what you would normally want too.

Let's create a new table, adding a primary key and also using the number all the time:

CREATE TABLE identity_tab (
 id   NUMBER GENERATED ALWAYS AS IDENTITY,
 name VARCHAR2(30),
 constraint id_tab_pk PRIMARY KEY (id)
);

Now add similar records again:

INSERT INTO identity_tab (name) VALUES ('TEST ROW1');
INSERT INTO identity_tab VALUES (2, 'TEST ROW2');
INSERT INTO identity_tab (Name) VALUES ('TEST ROW2');
COMMIT;

This time you will get an error message for the second row, but no duplicate IDs in the table!

SELECT * FROM IDENTITY_TAB;

See: https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1 for further information.

Creating a Table with an AS clause (CTAS)

It is possible to create a table and copy rows into it in a single operation.

To create the table BONUS and copy rows into it, type:

CREATE TABLE BONUS (ENAME, JOB, SAL , COMM) AS (	
  SELECT ENAME, JOB, SAL, COMM
  FROM EMP
  WHERE JOB = 'MANAGER' or COMM > 0.25 * SAL );

Note that the only employees eligible for the bonus are managers or those whose commission is more than 25% of their salary.

Copying rows from one table to another

You can use INSERT to select rows from one table and copy them into another.

To add employees whose commission is greater than 10% of their salary:

INSERT INTO BONUS(ENAME, JOB, SAL, COMM)
  SELECT ENAME, JOB, SAL, COMM
  FROM EMP WHERE COMM > 0.10 * SAL; 
COMMIT; 

Next Step

Dropping tables.