Rules and SYNTAX to Create a Table

Rule to Create a table in Oracle:

                                                        For more details on this topic follow the link : https://youtu.be/yxMqscOga_4
  • The user should have permission on CREATE TABLE command, and storage area should be allocated.
  • The table name should begin with a letter and can be 1-30 character long.
  • Table names can contain combination of A-Z , a-z , 0-9 ,  _ , @, #, `, ~, !, @, #, $, %, ^, &, *, (,) etc. 
  • Table names cannot be duplicated for another object name in the same Oracle server.
  • Table names cannot be Oracle server reserved keywords, but if we use the keyword within double quote then it is valid and allowed as a character string.
  • The table name is not case sensitive in Oracle, but if we use any specific (Upper/ Lower) case string within double quote then it is case sensitive while accessing the same tables.
  • The table is a collection of attribute names and Oracle datatypes, along with required constraints.

SYNTAX for Create table in Oracle:

CREATE TABLE table_name
  column1 datatype (width) [ NULL | NOT NULL ],
  column2 datatype (width) [ NULL | NOT NULL ],
  …
  column_n datatype [ NULL | NOT NULL ],
  [ CONSTRAINT [constraint_name] constraint_type (column_name), ]
  [ CONSTRAINT [constraint_name] constraint_type (column_name), ]
  … 
);
table_name:
Table_name is the name of the table that you wish to create.
column1, column2, … column_n:
column_name is the name of columns that you wish to create in the table. 
Each column must have a datatype. 
The column should either be defined as “null” or “not null” and if this value is left blank, the database assumes “null” as the default.
No two column in the same table can have the same name.
Maximum number of columns per table is 1000 in Oracle 8i, 9i, 10g, 11g
earlier in Oracle 7 it was possible to have only 254 columns maximum.
width:
Width is the maximum column size/width. All dataype may not have width property.
constraint_name:
Constraint_name is the name of the constraint, to identify. 
Constraint name is optional.
constraint_type:
The constraint is optional to create a table, whereas if we are including any constraint in a table then, constraint_type is mandatory.
Ex: PRIMARY KEY, FOREIGN KEY .. REFERENCE, NOT NULL, CHECK etc
We can provide these constraints in two ways:
 1. In-line constraint  /  Column Level constraint
 2. Out-of-line constraint   /  Table Level constraint.
Per table maximum number of columns allowed is 1000 in Oracle 8i, 9i, 10g, 11g
earlier in Oracle 7 there are 254 columns maximum
Example:
CREATE TABLE admin_emp (
  empno     NUMBER(5)          PRIMARY KEY,
  ename      VARCHAR2(15)    NOT NULL,
  job            VARCHAR2(10),
  mgr          NUMBER(5),
  hiredate   DATE DEFAULT (sysdate),
  deptno     NUMBER(3)          NOT NULL,
  photo       BLOB,
  sal             NUMBER(7,2),
  comm       NUMBER(7,2),
     CONSTRAINT admin_dept_fkey FOREIGN KEY (deptno) REFERENCES departments(department_id));

For more details on this topic follow the link : https://youtu.be/yxMqscOga_4

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn