|
|
| (14 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 the appendix: [[Oracle_Setup_Code|Oracle Setup Code]]
| + | **[[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:
| |
| − | | |
| − | {| class="wikitable" border="2" cellpadding=2 cellspacing=2
| |
| − | !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 (
| |
| − | 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)
| |
| − | );
| |
| − | | |
| − | The following constraints have been implemented on the project table PROJ:
| |
| − | | |
| − | * PROJNO is the primary key and is a number because we will use a sequence to generate the next number automatically when a new record is inserted. | |
| − | * BUDGET cannot have a null value. | |
| − | * PNAME is a string of 32 characters. | |
| − | * BUDGET is a number of maximum width 8 characters and with two decimal places. | |
| − | | |
| − | === Composite Primary and Foreign Keys ===
| |
| − | | |
| − | The primary key (PK) for tables with composite primary keys are defined slightly different from above, the clause PRIMARY KEY is not put after each field taking part in the PK, but as a separate clause. For example:
| |
| − | | |
| − | <code>
| |
| − | CREATE TABLE Tablename1 (
| |
| − | : col1 VARCHAR2(10), col2 VARCHAR2(10), col3 VARCHAR2(3),
| |
| − | : CONSTRAINT PK_TAB PRIMARY KEY (col1,col2)
| |
| − | );
| |
| − | </code>
| |
| − | | |
| − | Single foreign keys (FK) can be defined using the REFERENCES clause (see the definition for the EMP table in Appendix A for an example).
| |
| − | | |
| − | Composite foreign keys can be defined using the FOREIGN KEY clause, for example:
| |
| − | | |
| − | <code>
| |
| − | CREATE TABLE Tablename2 (
| |
| − | : col1 VARCHAR2 (10), col2 VARCHAR2 (10), ......,
| |
| − | : CONSTRAINT FK_TAB2 FOREIGN KEY (col1,col2) REFERENCES Tablename1); | |
| − | </code>
| |
| − | | |
| − | This ensures that any values put into the col1 and col2 columns exist in the related columns of Tablename1 so enforcing referential integrity.
| |
| − | | |
| − | If the column names are not the same in both tables, then the name of the related fields must also be specified:
| |
| − | | |
| − | <code>
| |
| − | CREATE TABLE Tablename3(newcol1 VARCHAR2 (10), newcol2 VARCHAR2 (10), ......,
| |
| − | : CONSTRAINT FK_TAB3 FOREIGN KEY (newcol1,newcol2)
| |
| − | : REFERENCES Tablename1 (col1,col2))
| |
| − | </code>
| |
| − | | |
| − | For example, to create a table EMPPROJ that records which projects an employee works on:
| |
| − | | |
| − | CREATE TABLE EMPPROJ (
| |
| − | EMPNO NUMBER(4) constraint fk_emp REFERENCES EMP,
| |
| − | PROJNO NUMBER(5) constraint fk_proj REFERENCES PROJ,
| |
| − | HOURS NUMBER(4),
| |
| − | CONSTRAINT pk_empproj PRIMARY KEY(empno, projno)
| |
| − | );
| |
| − | | |
| − | To create a further table that keeps a history of an employee’s expenses per project:
| |
| − | | |
| − | CREATE TABLE EMPPROJ_EXPENSES (
| |
| − | EMPNO NUMBER(4),
| |
| − | PROJNO NUMBER(5),
| |
| − | CLAIMDATE DATE,
| |
| − | CLAIMTOTAL NUMBER(7,2),
| |
| − | CONSTRAINT pk_ep_exp PRIMARY KEY(empno, projno, claimdate),
| |
| − | CONSTRAINT fk_ep FOREIGN KEY(empno, projno) REFERENCES EMPPROJ
| |
| − | );
| |
| − | | |
| − | == Creating a Sequence for a Table's Primary Key ==
| |
| − | | |
| − | The PROJNO attribute is the primary key for the PROJ table. Whenever a new row is inserted into this table a new unique number needs to be allocated. Oracle allows you to do this automatically if you create a sequence for a TABLE.
| |
| − | | |
| − | To do this first create the sequence:
| |
| − | | |
| − | CREATE SEQUENCE PROJSEQ;
| |
| − | | |
| − | Now it can be used instead of a primary key value:
| |
| − | | |
| − | INSERT INTO PROJ VALUES
| |
| − | (PROJSEQ.NEXTVAL,'ORACLE VERSION 10',15000);
| |
| − | COMMIT;
| |
| − | | |
| − | The sequence can also be used to add a record to a related table, such as the EMPPROJ table, so long as you add the employees to a particular project at the same time. For example, to add a new project and an employee at the same time:
| |
| | | | |
| − | INSERT INTO PROJ VALUES
| |
| − | (PROJSEQ.NEXTVAL,'ORACLE DEVELOPER',10000);
| |
| − | INSERT INTO EMPPROJ VALUES (7902, PROJSEQ.CURRVAL, 20);
| |
| − | COMMIT;
| |
| | | | |
| − | Note, the above 2 inserts form part of a transaction, so the commit is only needed after the last insert.
| + | ---- |
| | | | |
| − | NEXTVAL returns the next available number, incremented automatically, whereas CURRVAL returns the current value.
| + | Return to the [[Oracle_Workbook|Workbook]]. |
