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);

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.

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.

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.

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.

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.

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

 

Leave a Reply

  • (will not be published)