Table Creation and Management in Oracle

Share

SQL commands are used to create and modify tables, assign constraints on columns, add data to tables, and edit existing data. This blog post addresses methods for creating tables and modifying existing tables. Commands used to create or modify database tables are called data definition language (DDL) commands.
These commands are basically SQL commands used specifically to create or modify database objects. A
database object is a defined, self-contained structure in Oracle 12but could be used in 11g as well.
In this section, you create database tables, which are considered as database objects.




Table Design

Before issuing an SQL command to create a table, you must complete the relational model design. For each relation, you must choose the relation’s name and determine its structure—that is, what columns to include in the relation. In addition, you need to determine the width of any character or numeric columns.

Take a look at these requirements in more depth. Oracle 12c has the following rules for naming both tables and columns:

The names of tables and columns can be up to 30 characters and must begin with a letter. These limitations apply only to a table or column name, not to data in a column.
The names of tables and columns cant contain any blank spaces.
Numbers, the underscore symbol (_), and the number sign (#) are allowed in table and column names.
Each table owned by a user should have a unique table name, and the column names in each table should be unique.
Oracle 12c reserved words,such as SELECT, DISTINCT, CHAR, and NUMBER, etc. cant be used for table or column names.

The relational model has already been prepared using MySQL Workbench.

Figure : 1.1 Relational Model (entity diagram in Oracle, Crow’s Foot Model)

Now that the tables contents have been determined, the columns can be designed. When you create a table in Oracle 12c, you must define each column. Before you can create the columns, however, you must do the following:

Choose a name for each column.
Determine the type of data each column stores.
Determine (in some cases) the columns maximum width.

You need to identify the type of data to be stored in each column so that you can assign an appropriate datatype for each column.  Oracle data types:




Oracle currently has two variable-length character datatypes: VARCHAR and VARCHAR2. However, Oracle recommends using VARCHAR2 rather than VARCHAR, so VARCHAR2 is used throughout this post. Oracles SQL reference states:

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate datatype used for variable-length character strings compared with different comparison semantics.

 

The general syntax to create a table in Oracle is :

Optionally, a schema can be included to indicate who ownsthe table. For example, if the person
creating the table is also the person who owns the table, the schema can be omitted, and the current username is assumed by default.
  A database objects owner has the right to perform certain operations on that object. With a table, the only way another database user can query or manipulate data in the table is to be given permission from the tables owner or the database administrator. The table name, of course, is the name used to identify the table being created.

Many database objects, such as tables, supporting an application need to be shared by many users. In this case, users must be granted permission to access objects in the schema in which they reside. Also, to create a table in a schema other than your own, you must be granted permission to use the CREATE TABLE command for that schema.

 

In order to view the list of the tables, use the following SQL command

In order to view the structure of any specific table, use the following SQL command

In case, to view the structure of the table Expenses, you would execute the following SQL command

In case you do not need the table any more,  use the DROP command to remove the table:

Otherwise, you can still modify the changes required in the structure of the table to answer newer changes in the data requirements using ALTER Command.

Would You Mind Sharing?

Share
About Dhiraj 52 Articles
Blogger on development, securities, and efficiency on web technologies.

Be the first to comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.