Difference between revisions of "Oracle:Data Definition Language"

From mi-linux
Jump to navigationJump to search
(Created page with "== Data Definition Language Commands (DDL) == This section looks at how tables can be created. === Creating a Table === The code that produced the sample database can be se...")
 
 
(21 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
[[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> DDL
 +
 
== Data Definition Language Commands (DDL) ==
 
== Data Definition Language Commands (DDL) ==
  
−
This section looks at how tables can be created.
+
This section looks at how tables can be created, altered or dropped:
−
 
+
* [[Oracle:DDL_Create|Creating a table]]
−
=== Creating a Table ===
+
** [[Oracle:DDL_Create#Composite Keys|Composite Keys]]
−
 
+
**[[Oracle:DDL_Create#Sequences|Creating a Sequence]]
−
The code that produced the sample database can be seen in Appendix A.
+
**[[Oracle:DDL_Create#Identity|Identity Columns]]
−
 
+
**[[Oracle:DDL_Create#CTAS|Create Table AS...]]
−
The following command illustrates how the sample DEPT table was created. DO NOT TYPE THIS COMMAND IN!
+
* [[Oracle:DDL_Drop|Dropping a table]]
−
 
+
* [[Oracle:DDL_Alter|Altering a table]]
−
<pre>
+
** [[Oracle:DDL_Alter#Alter Column|Altering a column]]
−
CREATE TABLE DEPT (
+
** [[Oracle:DDL_Alter#Adding Column|Adding a column]]
−
DEPTNO NUMBER(2) constraint PK_DEPT primary key,
+
** [[Oracle:DDL_Alter#Delete Column|Deleting a column]]
−
DNAME VARCHAR2(14) NOT NULL constraint UC_DNAME check (DNAME=upper(DNAME)),
 
−
LOC VARCHAR2(13) constraint UC_LOC check (LOC=upper(LOC))
 
−
);
 
−
</pre>
 
−
 
 
−
The figure in brackets after the data type indicates the length.
 
−
 
 
−
The following constraints have been implemented on the Department table to ensure that the integrity of the database is maintained:
 
−
* The department number attribute DEPTNO is the primary key because of the constraint PK_DEPT. This also means that the user must insert a value into this attribute when inserting or updating information in this table.
 
−
* The department name attribute DNAME must be entered because of the NOT NULL clause and it must be in uppercase because of the constraint UC_DNAME.
 
−
* The location attribute LOC may be left blank because of the absence of a NOT NULL clause. If it is entered it must be in uppercase because of the constraint UC_LOC.
 
−
 
 
−
The constraint names (e.g. PK_DEPT) are specified so that Oracle can use these as labels for the constraint in its Data Dictionary.
 
−
 
 
−
The basic valid data types are:
 
  
−
Type Precision Scale Description
 
−
CHAR n - Fixed character of width n
 
−
DATE - - Date
 
−
NUMBER n d Numeric of width n with d decimal places
 
−
VARCHAR2 N - Variable character of width n
 
  
−
The following command creates a table called PROJ with three columns PROJNO, PNAME and BUDGET. You should type this in:
+
----
  
−
CREATE TABLE PROJ (
+
Return to the [[Oracle_Workbook|Workbook]].
−
    PROJNO NUMBER(5) constraint PK_PROJ primary key,
 
−
    PNAME VARCHAR2(32) constraint UC_PNAME check (PNAME=upper(PNAME)),
 
−
    BUDGET NUMBER(8,2) NOT NULL
 
−
    constraint BD_BUDGET check (BUDGET >= 0)
 
−
  );
 

Latest revision as of 17:19, 23 June 2016

Main Page >> Oracle and SQL >> Workbook >> DDL

Data Definition Language Commands (DDL)

This section looks at how tables can be created, altered or dropped:


Return to the Workbook.

Retrieved from "https://mi-linux.wlv.ac.uk/wiki/index.php?title=Oracle:Data_Definition_Language&oldid=9012"