DDL Script

DDL is a language that is used to manage or define a table.

Statements is:

1. CREATE
2. ALTER
3. RENAME
4. DROP
5. TRUNCATE

In this article we will discuss about how to make table using the CREATE

In the manufacture of table and column names are the requirements to be met are:

1. Must begin with alphabetic
2. name length must be 1-30 characters
3. Can only use AZ, az, 0-9, _, $, and #
4. The table name and the name of His column should not be the same as the names and columns in other tables, the same user
5. May not use names that have been reserved by the Oracle Server
   such as: FROM, INSERT, WHERE, and others

Syntax is as follows:

CREATE TABLE [table_name] (

[Column_name] [type_kolom] [size column]

)

-Create a table MsEmployee

CREATE TABLE MsEmployee
(
EmployeeID CHAR (5),
EmployeeName VARCHAR (20),

Email varchar2 (20),
Address varchar2 (30),
Salary NUMBER (10,2)

)

-Create a table TrHeaderTransaction

CREATE TABLE TrHeaderTransaction

(
TransactionID CHAR (5),
CustomerlD CHAR (5),
TransactionDate DATE

)

Keep in mind, the table above belom interconnected. We can connect the two tables by using Constraint.

Usefulness CONSTRAINT:

1. Creating rules in the table
2. Avoid the pen-delete-an on the table if the table has a dependence
3. Constraint Type is valid is as follows:

1. PRIMARY KEY
2. FOREIGN KEY
3. NOT NULL
4. UNIQUE
5. CHECKED

Guidelines in using Constraint:

1. We can provide the constraint name manually or can be generates by the Oracle Server using the command SYS_cn
2. Constraint-making can be conducted simultaneously at the time we create a table or the table is completed by using the Alter Table command
3. Defining the constraint can be performed on the column level or table level

CONSTRAINT [nama_constraint] [TYPE_CONSTRAINT]

-Making MsEmployee tables by using column-level constraint

CREATE TABLE MsEmployee
(
EmployeeID CHAR (5) PRIMARY KEY NOT NULL,
Cs002 CONSTRAINT CHECK (LENGTH (EmployeeID) = 5),
EmployeeName VARCHAR2 (20) NOT NULL,
Email varchar2 (20) UNIQUE,
Address varchar2 (30) NOT NULL,
Salary NUMBER (10,2) NOT NULL

)

-Making TrHeaderTransactiondengan tables using table-level constraint

CREATE TABLE TrHeaderTransaction
(
TransactionID CHAR (5) NOT NULL,
EmployeeID CHAR (5) NOT NULL,
TransactionDate DATE NOT NULL,
AAB CONSTRAINT PRIMARY KEY (TransactionID),
Cs005 CONSTRAINT CHECK (LENGTH (TransactionID) = 5),
Ngehubungin CONSTRAINT FOREIGN KEY (EmployeeID) REFERENCES MsEmployee (EmployeeID) ON DELETE CASCADE

)

Explanation:

• NOT NULL CONSTRAINT is used to ensure the column is filled so that no value is empty
• CONSTRAINT CHECKED used to check whether the data has been in accordance with predefined rules. In the above examples should be 5 characters long TransactionID not be less or more
• UNIQUE CONSTRAINT is used to ensure there are no charging value or the same name on other data so as to prevent duplication of data
• “Ngehubungin CONSTRAINT FOREIGN KEY (EmployeeID) REFERENCES MsEmployee (EmployeeID)” is used to link tables by table TrHeaderTransaction MsEmployee using EmployeeID EmployeeID as the connecting which is PRYMARY MsEmployee KEY of the table. Where Table MsEmployee as a parent and a child TrHeaderTransaction
• CONSTRAINT ON DELETE CASCADE is used to ensure the event’s pen-delete the parent table row, child rows referencing the value of the data is also deleted but not vice versa.

In Oracle we can also use DEFAULT Syntax as initial value data
example:

CREATE TABLE EMPLOYEES2 (
Hire_Date DATE DEFAULT SYSDATE / * hire_date column filled with a date now without doing the INSERT * /
)

Copying or copy a table

In addition to creating the table above we can also make a table of the contents and data types together with other tables

example:

CREATE MsEmp2
U.S.
SELECT * FROM MsEmployees

we also can determine which columns are willing to be copied

CREATE MsEmp2
U.S.
EmloyeeId SELECT, FROM EmployeeName MsEmployees

above query is used to copy the table along with its contents, if we want to copy only the table columns without data content that is by adding WHERE 1 = 2

example:

CREATE MsEmp2
U.S.
MsEmployees SELECT * FROM WHERE 1 = 2