Create Table Scripts

Create Table script is one of the initial scripts to be learnt when learning PL/SQL. In this post, we shall look at the variations of creating a table script with few columns and data in it.

1: Basic CREATE Table.

Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME_1 DATATYPE, COLUMN_NAME_2 DATATYPE);

CREATE TABLE xx_test_table
(
    emp_id       NUMBER,
    emp_name     VARCHAR2 (200),
    join_date    DATE
);

INSERT INTO xx_test_table
     VALUES (1001, 'Shane Smith', '01-AUG-2001');

INSERT INTO xx_test_table
     VALUES (1002, 'Steve Warne', '02-AUG-2002');

SELECT * FROM xx_test_table;

Result:

2: Create Table with Default Value for columns.

A default value can be provided for columns in a table in case a value is not provided in the Insert statement. In that scenario, the default value is populated in that column.

CREATE TABLE xx_test_table
(
    emp_id       NUMBER (6, 2),
    emp_name     VARCHAR2 (200) DEFAULT 'John Doe',
    join_date    DATE
);

INSERT INTO xx_test_table
     VALUES (1001, 'Shane Smith', '01-AUG-2001');

INSERT INTO xx_test_table
     VALUES (1002, 'Steve Warne', '02-AUG-2002');

INSERT INTO xx_test_table (emp_id, join_date)
     VALUES (1003, '02-AUG-2002');


SELECT * FROM xx_test_table;

Result:

3. NOT NULL on a column

During creation of a table, it can be mentioned that a particular column cannot have NULL values in it. In the example below, the column Join_date has a NOT NULL condition on it.

CREATE TABLE xx_test_table
(
    emp_id       NUMBER (6, 2),
    emp_name     VARCHAR2 (200) DEFAULT 'John Doe',
    join_date    DATE NOT NULL
);

INSERT INTO xx_test_table
     VALUES (1001, 'Shane Smith', '01-AUG-2001');

INSERT INTO xx_test_table
     VALUES (1002, 'Steve Warne', '02-AUG-2002');

INSERT INTO xx_test_table (emp_id, join_date)
     VALUES (1003, '02-AUG-2002');

INSERT INTO xx_test_table
     VALUES (1004, 'Mike Hannigan', NULL);

Thus, in the last line, when we try to insert a NULL value in to the Join_date column, we get the following error message.

“ORA-01400: cannot insert NULL into (“APPS”.”XX_TEST_TABLE”.”JOIN_DATE”)”

4. Defining Constraints on Columns

A constraint is an optional clause while creating a table. A constraint is a rule according to which the data is loaded into that table. A constraint can be defined on column level as well as at table level.

Usually the constraints follow the following naming standards: <tablename>_<column_name>_<constraint_abbreviation>

Column Level Constraints: These constraints refer to a single column on the table and do not specify a column name.

Primary Key: Indicates that data in each row of the column can be identified uniquely using this primary key. The identified column must be defined as NOT NULL. In the example below, the emp_id column is being assigned as a Primary Key.

CREATE TABLE xx_test_table
(
    emp_id       NUMBER (6, 2) NOT NULL CONSTRAINT xx_test_emp_id_PK PRIMARY KEY,
    emp_name     VARCHAR2 (200) DEFAULT 'John Doe',
    join_date    DATE NOT NULL
);

INSERT INTO xx_test_table
     VALUES (1001, 'Shane Smith', '01-AUG-2001');

INSERT INTO xx_test_table
     VALUES (1002, 'Steve Warne', '02-AUG-2002');

INSERT INTO xx_test_table (emp_id, join_date)
     VALUES (1003, '02-AUG-2002');

INSERT INTO xx_test_table
     VALUES (1001, 'Mike Hannigan', '01-SEP-2002');

Thus, when a duplicate value is being inserted in the 4th line, a Primary Key Violation is being thrown.

Unique: Indicates that the values in the column must be unique. In the example below, the emp_id column has a Unique constraint on it.

CREATE TABLE xx_test_table
(
    emp_id       NUMBER (6, 2) CONSTRAINT xx_test_emp_id_UQ UNIQUE,
    emp_name     VARCHAR2 (200) DEFAULT 'John Doe',
    join_date    DATE NOT NULL
);

INSERT INTO xx_test_table
     VALUES (1001, 'Shane Smith', '01-AUG-2001');

INSERT INTO xx_test_table
     VALUES (1002, 'Steve Warne', '02-AUG-2002');

INSERT INTO xx_test_table (emp_id, join_date)
     VALUES (1003, '02-AUG-2002');

INSERT INTO xx_test_table
     VALUES (1001, 'Mike Hannigan', '01-SEP-2002');

Thus, when we try to insert a value of 1001 into emp_id, when the value already exists, then the command throws an error.

CHECK: The CHECK constraint specifies certain rules for the data in the column according to which the user likes the data to be. In the example below, the CHECK constraint makes sure that the Salary is always above 5000.

CREATE TABLE xx_test_table
(
    emp_id       NUMBER (6, 2),
    emp_name     VARCHAR2 (200) DEFAULT 'John Doe',
    join_date    DATE NOT NULL, 
    salary       NUMBER (8,2) CONSTRAINT sal_chk CHECK (salary > 5000)
);

INSERT INTO xx_test_table
     VALUES (1001, 'Shane Smith', '01-AUG-2001', 8500);

INSERT INTO xx_test_table
     VALUES (1002, 'Steve Warne', '02-AUG-2002', 7200);

INSERT INTO xx_test_table
     VALUES (1001, 'Mike Hannigan', '01-SEP-2002', 4800);

Thus, when we try to insert the third line above, with a salary of 4800 (less than 5000), the insert command throws an error.

Create Table Scripts

Phani Adivi

Recent Posts