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...")
 
Line 11: Line 11:
 
<pre>
 
<pre>
 
CREATE TABLE DEPT (
 
CREATE TABLE DEPT (
DEPTNO NUMBER(2) constraint PK_DEPT primary key,
+
DEPTNO NUMBER(2) constraint PK_DEPT primary key,
DNAME VARCHAR2(14) NOT NULL constraint UC_DNAME check (DNAME=upper(DNAME)),
+
DNAME VARCHAR2(14) NOT NULL constraint UC_DNAME check (DNAME=upper(DNAME)),
LOC VARCHAR2(13) constraint UC_LOC check (LOC=upper(LOC))  
+
LOC VARCHAR2(13) constraint UC_LOC check (LOC=upper(LOC))  
 
);
 
);
 
</pre>
 
</pre>
Line 28: Line 28:
 
The basic valid data types are:
 
The basic valid data types are:
  
Type Precision Scale Description
+
{|
CHAR n - Fixed character of width n
+
!Type!!Precision!!Scale!!Description
DATE - - Date
+
|-
NUMBER n d Numeric of width n with d decimal places
+
CHAR n||-||Fixed character of width n
VARCHAR2 N - Variable 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:
 
The following command creates a table called PROJ with three columns PROJNO, PNAME and BUDGET. You should type this in:

Revision as of 14:22, 1 March 2016

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 seen in Appendix A.

The following command illustrates how the sample DEPT table was created. DO NOT TYPE THIS COMMAND IN!

CREATE TABLE DEPT (
DEPTNO 	NUMBER(2) constraint PK_DEPT primary key,
DNAME	VARCHAR2(14) NOT NULL 	constraint UC_DNAME check (DNAME=upper(DNAME)),
LOC	VARCHAR2(13) constraint UC_LOC check (LOC=upper(LOC)) 
);

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:

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
Type Precision Scale Description

The following command creates a table called PROJ with three columns PROJNO, PNAME and BUDGET. You should type this in:

CREATE TABLE PROJ (
   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)
 );