Managing Tables and Data in Oracle,Syntax Of Table

1.CREATING A TABLE

In order to create a table, the DDL, create is used and the columns of the table along with the data typ0es and the width are to be specified. The names given to the tables and its columns must follow certain rules. When a table is created, Oracle puts the table definition into the data dictionary.

Rules for Table and Column Names

·         Names can be up to 30 characters.

·         Names must begin with an alphabet.

·         Names cannot contain quotes.

·         Names are NOT case sensitive.

·         Names can contain characters, a-z, 0-9,_, $ and #.

·         Database names can be up to 8 characters.

·         Names cannot be reserved words.

·         The above rules can be bypassed by using double quotes. 

 Syntax 1

                        CREATE TABLE <table-name>

                        (Field-name1 Field-type (width),

                        Field-name2 Field-type (width),

                        Field-name3 Field-type (width) ); 

Example: - Let us create a table to store STUDENT data like the roll number, name and the birth date. 

SQL>CREATE TABLE student (roll_no number (5),name  char (20), Sbirth_date date);

Note: For the data type of date, width must not be specified. 

 

Syntax 2

As the fields are names, certain constraints can be specified. Constraints are rules to control the data in a column. The common constraints include Primary key, Not Null, and Check constraint.

CREATE TABLE <table-name>

(

            Field-name1 Field-type  (width)  PRIMARY KEY,

            Field-name2 Field-type  (width)  NOT NULL,

            Field-name3 Field-type  (width)  CHECK (field-name in (‘a’,’b’,’c’))

);

PRIMARY KEY :- This is a constraint for the column of a table. The Primary key constraint ensures that the column cannot be null and that the values in the column will be unique.

NOT NULL :- The NOT null constraint ensured that the user always type the data for that column.

CHECK :- The check constraint ensures that when data is entered, the data in the column is limited to specific values, like a, b or c.