|
|
| (19 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]] |
| − | <code>
| + | ** [[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))
| |
| − | );
| |
| − | </code>
| |
| − | | |
| − | 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)
| |
| − | );
| |
