FB

Oracle Create TableCreating database tables in Oracle is one of the most common tasks an Oracle developer or Oracle DBA does. Learn how to create tables, what the syntax is, and see some examples in this article.

What Is The Create Table Command Used For?

The SQL CREATE TABLE command is used to create a database table.

It can be used to create different types of database tables, such as temporary tables. However, in this article, I’ll only cover the regular database table.

 

Oracle Create Table Syntax

The syntax for the SQL create table statement is:

CREATE schema_name.table_name
(
column_name data_type [NULL | NOT NULL] [inline_constraint] [DEFAULT default_value],

out_of_line_constraints
)
TABLESPACE tablespace_name;

 

The parameters or values mentioned in this syntax are:

schema_name

This is the schema that the table will be created under. It needs to be followed by a period and then the table name.

table_name

This is the name of the table that you want to create. It can be up to 30 characters long.

It’s a good idea not to include spaces in the table name, to make development easier. I also recommend using singular table names (read more on singular vs plural here)

column_name

This is the name of each column that can be stored in the database table. It can also be up to 30 characters.

data_type

The data type that the column relates to. Many data types require brackets after the data type to specify the number of characters or digits. You can find a full list of data types on Oracle’s website.

NULL|NOT NULL

This is used to specify if the column values are allowed to be NULL or not.

  • NULL means the column can be set to NULL. If you don’t specify the value when inserting, or specify the value of NULL, the statement won’t cause an error and the data will be inserted.
  • NOT NULL means the column cannot be set to NULL. If you don’t specify the value when inserting, or specify the value of NULL, the statement will cause an error.

inline_constraint

You can define a constraint here on this column. This is what is called an “inline constraint”, because you define it on the same line as the column.

The difference between inline and out of line constraints is that you can’t define a constraint with multiple columns inline, and NOT NULL constraints can only be defined inline.

default_value

If you want to have a default value on this column when it is not specified during an INSERT statement, you can specify it here. An example of this would be a created_date column – it doesn’t need to have its value specified when you insert it, so the default could be SYSDATE.

out_of_line_constraints

Here, you can specify table constraints, which are called out of line constraints. They work in the same way as inline constraints, except you can’t specify NOT NULL constraints here.

For example you may want to add a primary key or foreign key here.

tablespace_name

The tablespace_name parameter specifies the tablespace that the table is created in. I won’t cover what a tablespace is in this article, but in the SQL CREATE TABLE statement, this is how you specify it.

Now, there’s a whole lot of parameters that are not mentioned here (such as STORAGE and NOLOGGING), because they are not relevant for most database users.

 

Oracle Create Table Primary Key Syntax

You can specify a primary key on a table when you create in two ways:

  1. Next to the column data type when you declare it on the same line (an inline constraint)
  2. At the end of all column declarations (an out of line constraint)

The method you choose would be dependent on your team’s standards and whether or not you need to have two or more columns in your primary key (you can only specify one column in an inline constraint).

To declare a primary key inline using a CREATE TABLE statement:

CREATE table_name
(
column_name data_type PRIMARY_KEY,

);

That’s it. You just add the words PRIMARY KEY after the data type. They are separate words – there’s no underscore or hyphen between them.

The out of line method of declaring a primary key in a Create Table command is a bit different.

CREATE table_name
(
column_name data_type,

CONSTRAINT pk_tbl1 PRIMARY KEY (column_name)
);

You need to add it after all of your column definitions. You also need to start with the word CONSTRAINT.

Then, give the primary key a name. You only need to do this for out of line constraints. In this example, I’ve given it the name of “pk_tbl1”, to indicate that it is a primary key, and the “tbl1” would be the name of the table.

Inside the brackets after the word PRIMARY KEY, you specify the column names. If there is more than one, separate them by a comma.

 

Oracle Create Table Foreign Key Syntax

Like the primary key definition, you can declare a foreign key both inline and out of line in the SQL Create Table command.

Inline foreign keys are declared like this:

CREATE table_name
(
column_name data_type REFERENCES other_table_name(other_column_name),

);

You use the word REFERENCES, then the name of the table that the the foreign key refers to, then within brackets you specify the column that the foreign key links to.

The other method of adding a foreign key using the Oracle CREATE TABLE command is the out of line method.

CREATE table_name
(
column_name data_type,

CONSTRAINT fk_tbl1_tbl2 FOREIGN KEY (this_tables_column)
REFERENCES other_table_name (other_column_name)
);

You need to start with the word CONSTRAINT, then the name of the foreign key. The name needs to be unique across the database, so I like to start with the term “fk”, then the two tables I am referring to.

Then, you specify the words FOREIGN KEY, then the name of the other table within brackets. Then you add the word REFERENCES, then within brackets you specify the column name from the table you’re referring to (which is probably the primary key).

 

Is There An Oracle CREATE TABLE IF NOT EXISTS Command?

In short, no, there isn’t. There is no Oracle CREATE TABLE IF NOT EXISTS command like in MySQL.

As mentioned in this StackOverflow answer:

Normally, it doesn’t make a lot of sense to check whether a table exists or not because objects shouldn’t be created at runtime and the application should know what objects were created at install time. If this is part of the installation, you should know what objects exist at any point in the process so you shouldn’t need to check whether a table already exists.

To check if a table exists before creating it, you’ll need to write a PL/SQL block. There are a few ways you can check:

  1. You can attempt to create a table, and catch the error that appears (ORA-00955: name is already in use by an existing object).
  2. You can query the USER_TABLES view to find a count where the table name matches, and check if the value is > 0.
  3. You can drop the table and then create it again.

 

Oracle CREATE TABLE AS SELECT

Oracle allows you to create a table based on a SELECT statement.

This is a very useful feature of the database.

It’s great for development – if you want to copy an existing table and use it for testing or development purposes without changing the real table. Or, if you want a similar table with the same structure.

The syntax for using this command is:

CREATE TABLE table_name
AS (SELECT select_query);

It’s also referred to as CTAS.

You specify the table_name for your new table, as well as the SELECT query to base it from.

If you want all records to be copied to the new table, you could specify SELECT * FROM old_table.

You can use a WHERE clause to restrict the values to be copied across.

Alternatively, you can create your table like normal, without the AS SELECT, and use an INSERT INTO SELECT to populate the records into the table.

 

Oracle CREATE TABLE AS SELECT Without Copying Data

You can also use the CREATE TABLE AS SELECT to copy a table’s structure without any of the data.

It’s easier than trying to generate a create table script from an existing table.

To do this, you just adjust your WHERE clause in the SELECT query to ensure there are no rows returned.

How do you guarantee this?

By adding something that is clearly evaluated to FALSE.

A common example is WHERE 1=0. Or WHERE 1=2. Really, anything involving two numbers that are not equal will work.

So, the SELECT statement will return the column names and data types, but no data. When the table is created, it will have no data in it.

The syntax for this would be:

CREATE TABLE table_name
AS (SELECT * FROM old_table WHERE 1=0);

Oracle CREATE TABLE Errors and Exceptions

These are some of the errors and exceptions that may appear when you’re creating a table. As a database developer, you’ll get these kind of errors all the time!

Exception: ORA-00955: name is already used by an existing object

Reason: You’re attempting to create a table with a name that is already being used.

Resolution: Use a different name for your table, or drop the table with the existing name. Or, use a different schema for your table if that’s applicable.

 

Exception: ORA-02260: table can have only one primary key

Reason: You’re attempting to add a primary key to a table that already has one.

Resolution: Review your SQL CREATE TABLE statement and remove all but one PRIMARY KEY definition.

 

Exception: ORA-02267: column type incompatible with referenced column type

Reason: The column you’re referring to is not compatible with the column you’re defining it on. This could happen when defining a foreign key.

Resolution: Make sure the data types are the same for both columns. And, make sure you’re not referring to the same table for the foreign key (it should be a different table).

 

Exception: ORA-00904: : invalid identifier

Reason: There are many reasons for this error, but it’s usually a syntax error.

Resolution: Check that you have all of your commas and brackets in the right places in your statement.

 

Exception: ORA-00957: duplicate column name

Reason: You have two columns with the same name.

Resolution: Rename one of your columns to make sure it is not a duplicate. Also, check that your commas and brackets are all in the right places.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle CREATE TABLE Examples

Alright, now it’s time to look at some examples of creating a table.

These Oracle CREATE TABLE examples cover all of the topics I’ve mentioned in this article. It usually helps to see examples with data and real names, rather than syntax.

Example 1 – Basic Table

This example uses a CREATE TABLE statement that creates a simple table with a couple of columns.

CREATE TABLE example1 (
table_id NUMBER(10),
first_name VARCHAR2(50)
);

Example 2 – Large Table

This CREATE TABLE example includes many different columns and data types.

CREATE TABLE example2 (
table_id NUMBER(10),
first_name VARCHAR2(50),
last_name VARCHAR2(200),
registration_date DATE,
registration_category CHAR(1),
upload_data BLOB
);

Example 3 – Large Table with NOT NULL and DEFAULT

This CREATE TABLE example is similar to Example 2, but includes several NOT NULL constraints and some default values.

CREATE TABLE example3 (
table_id NUMBER(10),
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(200) NOT NULL,
registration_date DATE DEFAULT SYSDATE,
registration_category CHAR(1) DEFAULT ‘B’
);

Example 4 – Inline Primary Key

This example of the CREATE TABLE statement uses a primary key that is defined inline.

CREATE TABLE example4 (
table_id NUMBER(10) PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(200)
);

Example 5 – Inline Primary Key and Foreign Key

This CREATE TABLE example uses an inline primary key and inline foreign key.

CREATE TABLE example5 (
table_id NUMBER(10) PRIMARY KEY,
example_table_id NUMBER(10) REFERENCES example4(table_id),
first_name VARCHAR2(50),
last_name VARCHAR2(200)
);

Example 6 – Out of Line Primary Key and Foreign Key

This Oracle CREATE TABLE example declares a primary key and foreign key out of line (at the end of the column declarations).

CREATE TABLE example6 (
table_id NUMBER(10),
example_table_id NUMBER(10),
first_name VARCHAR2(50),
last_name VARCHAR2(200),
CONSTRAINT pk_ex6 PRIMARY KEY (table_id),
CONSTRAINT fk_ex6_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id)
);

Example 7 – More Constraints

This example uses CREATE TABLE to declare a table with a primary key, foreign key, unique constraint, and check constraint.

CREATE TABLE example7 (
table_id NUMBER(10),
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(200) NOT NULL,
registration_date DATE DEFAULT SYSDATE,
registration_category CHAR(1) DEFAULT ‘B’,
registration_number NUMBER(10),
example_table_id NUMBER(10),
CONSTRAINT pk_ex7 PRIMARY KEY (table_id),
CONSTRAINT fk_ex7_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id),
CONSTRAINT ck_ex7_regcat CHECK (registration_category IN (‘B’, ‘C’, ‘W’, ‘P’)),
CONSTRAINT uc_ex7_regno UNIQUE (registration_category, registration_number)
);

Example 8 – Create Table as Select with All Columns

This example uses the Create Table as Select to create a table from another table, using all columns.

CREATE TABLE example8 AS
(SELECT *
FROM example7);

Example 9 – Create Table as Select with Some Columns

This example uses the Create Table as Select to create a table from another table, using only some of the columns.

CREATE TABLE example8 AS
(SELECT table_id, first_name, last_name
FROM example7);

Example 10 – Create Table as Select with No Data

This example uses the Create Table as Select to create a table from another table, but no data is added to the new table.

CREATE TABLE example8 AS
(SELECT table_id, first_name, last_name
FROM example7
WHERE 1=0);

So, there’s your guide to the Oracle CREATE TABLE statement. If you have any questions on any of the parts of an SQL CREATE TABLE statement, let me know in the comments below.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your FREE PDF: 9 Ways to Improve your Database Skills