Data Definition Language is a set of SQL commands used to create, modify and delete database structures (not data). These commands wouldn't normally be used by a general user, who should be accessing the database via an application.
They are normally used by the DBA (to a limited extent), a database designer or application developer. These statements are immediate, they are not susceptible to ROLLBACK commands.
You should also note that if you have executed several DML updates then issuing any DDL command will COMMIT all the updates as every DDL command implicitly issues a COMMIT command to the database.
Anybody using DDL must have the CREATE object privilege and a Tablespace area in which to create objects.
In an Oracle database objects can be created at any time, whether users are on-line or not. Table space need not be specified as Oracle will pick up the user defaults (defined by the DBA) or the system defaults.
Tables will expand automatically to fill disk partitions (provided this has been set up in advance by the DBA). Table structures may be modified on-line although this can have dire effects on an application so be careful.
Creating our two example tables:
CREATE TABLE BOOK (
ISBN NUMBER(10),
TITLE VARCHAR2(200),
AUTHOR VARCHAR2(50),
COST NUMBER(8,2),
LENT_DATE DATE,
RETURNED_DATE DATE,
TIMES_LENT NUMBER(6),
SECTION_ID NUMBER(3))
CREATE TABLE SECTION (
SECTION_ID NUMBER(3),
SECTION_NAME CHAR(30),
BOOK_COUNT NUMBER(6) )
The two commands above create our two sample tables and demonstrate the basic table creation command. The CREATE keyword is followed by the type of object that we want created (TABLE, VIEW, INDEX etc.), and that is followed by the name we want the object to be known by. Between the outer brackets lie the parameters for the creation, in this case the names, datatypes and sizes of each field.
A NUMBER is a numeric field, the size is not the maximum externally displayed number but the size of the internal binary field set aside for the field (10 can hold a very large number). A number size split with a comma denotes the field size followed by the number of digits following the decimal point (in this case a currency field has two significant digits)
A VARCHAR2 is a variable length string field from 0-n where n is the specified size. Oracle only takes up the space required to hold any value in the field, it doesn't allocate the entire storage space unless required to by a maximum sized field value (Max size 2000).
A CHAR is a fixed length string field (Max size 255).
A DATE is an internal date/time field (normally 7 bytes long).
A LONG or LONG RAW field (not shown) is used to hold large binary objects (Word documents, AVI files etc.). No size is specified for these field types. (Max size 2Gb).
Creating our two tables with constraints :
Constraints are used to enforce table rules and prevent data dependent deletion (enforce database integrity). You may also use them to enforce business rules (with some imagination).
Our two example tables do have some rules which need enforcing, specifically both tables need to have a prime key (so that the database doesn't allow replication of data). And the Section ID needs to be linked to each book to identify which library section it belongs to (the foreign key). We also want to specify which columns must be filled in and possibly some default values for other columns. Constraints can be at the column or table level.
<><><><><><><>
CREATE TABLE SECTION ( SECTION_ID NUMBER(3) CONSTRAINT S_ID CHECK (SECTION_ID > 0),
SECTION_NAME CHAR(30) CONSTRAINT S_NAME NOT NULL, BOOK_COUNT NUMBER(6),
CONSTRAINT SECT_PRIME PRIMARY KEY (SECTION_ID))
CREATE TABLE BOOK ( ISBN NUMBER(10) CONSTRAINT B_ISBN CHECK (ISBN BETWEEN 1 AND 2000), TITLE VARCHAR2(200) CONSTRAINT B_TITLE NOT NULL, AUTHOR VARCHAR2(50) CONSTRAINT B_AUTH NOT NULL, COST NUMBER(8,2) DEFAULT 0.00 DISABLE, LENT_DATE DATE, RETURNED_DATE DATE, TIMES_LENT NUMBER(6), SECTION_ID NUMBER(3),
CONSTRAINT BOOK_PRIME PRIMARY KEY (ISBN),
CONSTRAINT BOOK_SECT FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID))
We have now created our tables with constraints.
Column level constraints go directly after the column definition to which they refer, table level constraints go after the last column definition. Table level constraints are normally used (and must be used) for compound (multi column) foreign and prime key definitions, the example table level constraints could have been placed as column definitions if that was your preference (there would have been no difference to their function).
The CONSTRAINT keyword is followed by a unique constraint name and then the constraint definition.
The constraint name is used to manipulate the constraint once the table has been created, you may omit the CONSTRAINT keyword and constraint name if you wish but you will then have no easy way of enabling / disabling the constraint without deleting the table and rebuilding it, Oracle does give default names to constraints not explicitly name - you can check these by selecting from the USER_CONSTRAINTS data dictionary view.
Note that the CHECK constraint implements any clause that would be valid in a SELECT WHERE clause (enclosed in brackets), any value inbound to this column would be validated before the table is updated and accepted / rejected via the CHECK clause. Note that the order that the tables are created in has changed, this is because we now reference the SECTION table from the BOOK table.
The SECTION table must exist before we create the BOOK table else we will receive an error when we try to create the BOOK table. The foreign key constraint cross references the field SECTION_ID in the BOOK table to the field (and primary key) SECTION_ID in the SECTION table (REFERENCES keyword).
If we wish we can introduce cascading validation and some constraint violation logging to our tables.
CREATE TABLE AUDIT ( ROWID ROWID, OWNER VARCHAR2,TABLE_NAME VARCHAR2, CONSTRAINT VARCHAR2)
CREATE TABLE SECTION (SECTION_ID NUMBER(3) CONSTRAINT S_ID CHECK (SECTION_ID > 0), SECTION_NAME CHAR(30) CONSTRAINT S_NAME NOT NULL, BOOK_COUNT NUMBER(6), CONSTRAINT SECT_PRIME PRIMARY KEY (SECTION_ID),EXCEPTIONS INTO AUDIT)
CREATE TABLE BOOK ( ISBN NUMBER(10) CONSTRAINT B_ISBN CHECK (ISBN BETWEEN 1 AND 2000),
TITLE VARCHAR2(200) CONSTRAINT B_TITLE NOT NULL, AUTHOR VARCHAR2(50) CONSTRAINT B_AUTH NOT NULL, COST NUMBER(8,2) DEFAULT 0.00 DISABLE, LENT_DATE DATE, RETURNED_DATE DATE, TIMES_LENT NUMBER(6), SECTION_ID NUMBER(3),CONSTRAINT BOOK_PRIME PRIMARY KEY (ISBN),CONSTRAINT BOOK_SECT FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID)ON DELETE CASCADE)
Oracle (and any other decent RDBMS) would not allow us to delete a section which had books assigned to it as this breaks integrity rules. If we wanted to get rid of all the book records assigned to a particular section when that section was deleted we could implement a DELETE CASCADE. The delete cascade operates across a foreign key link and removes all child records associated with a parent record (we would probably want to reassign the books rather than delete them in the real world).
To log constraint violations I have created a new table (AUDIT) and stated that all exceptions on the SECTION table should be logged in this table, you can then view the contents of this table with standard SELECT statements. The AUDIT table must have the shown structure but can be called anything.
It is possible to record a description or comment against a newly created or existing table or individual column by using the COMMENT command. The comment command writes your table / column description into the data dictionary. You can query column comments by selecting against dictionary views ALL_COL_COMMENTS and USER_COL_COMMENTS.
You can query table comments by selecting against dictionary views ALL_TAB_COMMENTS and USER_TAB_COMMENTS. Comments can be up to 255 characters long, examples are given below :
COMMENT ON TABLE JD11.BOOK IS 'Library Book Information'
COMMENT ON
COLUMN JD11.BOOK.AUTHOR IS 'Authors Name'
Altering tables and constraints :
Modification of database object structure is executed with the ALTER statement.
You can modify a constraint as follows :-
Add new constraint to column or table.
Remove constraint.
Enable / disable constraint.
You cannot change a constraint definition.
You can modify a table as follows :-
Add new columns.
Modify existing columns.
You cannot delete an existing column.
An example of adding a column to a table is given below :
ALTER TABLE JD11.BOOK ADD (REVIEW VARCHAR2(200))
This statement adds a new column (REVIEW) to our book table, to enable library members to browse the database and read short reviews of the books.
If we want to add a constraint to our new column we can use the following ALTER statement :
ALTER TABLE JD11.BOOK MODIFY(REVIEW NOT NULL)
Note that we can't specify a constraint name with the above statement. If we wanted to further modify a constraint (other than enable / disable) we would have to drop the constraint and then re apply it specifying any changes.
Assuming that we decide that 200 bytes is insufficient for our review field we might then want to increase its size. The statement below demonstrates this :
ALTER TABLE JD11.BOOK MODIFY (REVIEW VARCHAR2(400))
We could not decrease the size of the column if the REVIEW column contained any data.
ALTER TABLE JD11.BOOK DISABLE CONSTRAINT B_AUTH
ALTER TABLE JD11.BOOK ENABLE CONSTRAINT B_AUTH
The above statements demonstrate disabling and enabling a constraint, note that if, between disabling a constraint and re enabling it, data was entered to the table that included NULL values in the AUTHOR column, then you wouldn't be able to re enable the constraint. This is because the existing data would break the constraint integrity. You could update the column to replace NULL values with some default and then re enable the constraint.
Dropping (deleting) tables and constraints :
To drop a constraint from a table we use the ALTER statement with a DROP clause. Some examples follow :
ALTER TABLE JD11.BOOK DROP CONSTRAINT B_AUTH
The above statement will remove the not null constraint (defined at table creation) from the AUTHOR column. The value following the CONSTRAINT keyword is the name of constraint.
ALTER TABLE JD11.BOOK DROP PRIMARY KEY
The above statement drops the primary key constraint on the BOOK table.
ALTER TABLE JD11.SECTION DROP PRIMARY KEY CASCADE
The above statement drops the primary key on the SECTION table. The CASCADE option drops the foreign key constraint on the BOOK table at the same time.
Use the DROP command to delete database structures like tables. Dropping a table removes the structure, data, privileges, views and synonyms associated with the table (you cannot rollback the DROP so be careful). You can specify a CASCADE option to ensure that constraints refering to the dropped table within other tables (foreign keys) are also removed by the DROP.
DROP TABLE SECTION
The above statement drops the table SECTION but leaves the foreign key reference within the BOOK table.
DROP TABLE SECTION CASCADE CONSTRAINTS
The above statement drops the table SECTION and removes the foreign key reference from the BOOK table.
Renaming Database Objects :
Use the RENAME command to change the name of tables, views and synonyms. Note that if you change an object's name you will have to update any references to it in any application or other database object that uses it. It is wise to decide on an object's name then stick with it, even if you don't like the name you will probably save yourself a lot of work if you just live with it.
RENAME JD11.BOOK TO JD11.LIBRARY_BOOK
Deleting all data from a table :
You can delete all the data in a table by using the TRUNCATE command (you can also achieve this with a DELETE command but it is not as efficient due to the ROLLBACK memory usage). TRUNCATE flushes the table of data but leaves the structure and constraints intact. You can add a REUSE STORAGE option which tells the table to hold onto the memory it used to store the rows (but I can't think of any reason why you would want to do this).
TRUNCATE TABLE BOOK
TRUNCATE TABLE BOOK REUSE STORAGE
Indexes :
Indexes are used to speed up row retrieval and enforce column uniqueness. Oracle automatically creates an index on any key column or any column with the UNIQUE constraint. Oracle stores indexes in index segments attached to the user's default tablespace, you may attach the index segment to another tablespace when you create an index if you think this will help performance.
Oracle normally uses balanced binary tree index structures as these give roughly equal access times to all table rows, each index consists of a number of pages of storage arranged in a hierarchical tree. Each page holds a series of key values and pointers to pages lower in the structure until eventually the keys point to the data rows themselves.
There are four index types, they are :-
UNIQUE - ensures that values in specified column(s) are unique.
NON UNIQUE - Ensures fastest possible results when querying / ordering data.
SINGLE COLUMN - Only a single column makes up the index.
CONCATENATED - Up to 16 columns make up the index.
Indexes can be created on-line, an example follows :
CREATE INDEX IDX_SECT_SECT ON JD11.SECTION(SECTION_NAME)
This index will speed up SELECT statements using the section name as the WHERE clause parameter.
CREATE UNIQUE INDEX IDX_SECT_SECT ON JD11.SECTION(SECTION_NAME)
This index will speed up SELECT statements using the section name in the WHERE clause and will force the SECTION_NAME column to contain distinctive values.
DROP INDEX IDX_SECT_SECT
The above statement removes the index.
I will not go through all the ins and outs of indexes here but here are a few important points.
For a SELECT statement to use an index it must have a WHERE clause which keys on the index column where the index column is not part of a function (like upper casing etc.).
For compound WHERE clauses the index column must be the first column keyed upon.
Ensure that joined tables have indexes on any columns that are used in the join, this can save a lot of time during execution because otherwise Oracle must individually sort each table, then merge the sorted lists together (this is very slow on large tables).
Index any column where unique values are required.
Index any column frequently used in WHERE clauses.
If two or more columns are frequently used in WHERE or JOIN conditions then create a CONCATENATED index with them.
Avoid overusing indexes - there is an operational overhead for each index.
Sequences :
A sequence generator is a construct used to generate sequential unique numbers, it is usually used to produce table key values where the table data has no clearly identifiable prime key or where the identification of rows within a table would involve a large, unwieldy compound primary key.
It is a shareable database object and its owner can grant access to it to other database users. This construct provides a similar function to (but much more flexibility than) the MS Access auto number. A sequence creation statement can have several parameters, the possibilities are listed below.
<><><><><><><>
We will now create a sequence to be used with our SECTION table, it will be used to generate the unique section numbers.
CREATE SEQUENCE SECIDNUM INCREMENT BY 1 START WITH 1 MAXVALUE 100 NOCACHE.
The value following the SEQUENCE key word is the name of the sequence generator, you will use this from now on to refer to the sequence. To get values from the sequence we use a SELECT statement like the one below, obviously this select would form part of the application creation event for a new library section.
SELECT SECIDNUM.NEXTVAL FROM SYS.DUAL
This select gets the next sequential number from the generator and updates the sequence with the INCREMENT BY value. Note that if you don't use the value it is thrown away, the sequence number does not guarantee uninterrupted number sequences (this might be an audit issue, especially on financial systems). The NEXTVAL keyword tells the generator to provide the next sequential value. You may replace NEXTVAL with CURRVAL to look at the current value of a sequence.
If we wanted to use the sequence to create a new library section in SQL *Plus rather than an application we could use a statement like this.
INSERT INTO JD11.SECTION VALUES (SECIDNUM.NEXTVAL, 'Computing', 0)
You may alter the INCREMENT BY, MINVALUE and MAXVALUE values of a sequence by using the ALTER command.
ALTER SEQUENCE SECIDNUM INCREMENT BY 2
ALTER SEQUENCE SECIDNUM MINVALUE 10
ALTER SEQUENCE SECIDNUM MAXVALUE 2500
You can remove a sequence by using the DROP command.
DROP SEQUENCE SECIDNUM
Views :
A view is a method of organising table data to meet a specific need. Views are based on SELECT statements which derive their data from real tables. A view allows you to reorganise the database data, you might want to do this so that you can restrict data access, reduce selection complexity, provide improved data independence or allow disparate users to view the same data in different ways. Most of the Oracle data dictionary is readable via views which interpret internal Oracle tables. Views come in simple and complex forms. Simple views are based on a single table and data can be updated via DML commands (privilege issues aside), complex views are derived from multiple tables and DML commands cannot be used to update data.
If we wanted our front desk librarians to only see lending data from the BOOK table we could create a view that fulfilled this need.
CREATE VIEW BOOKLEND AS
SELECT ISBN, TITLE, LENT_DATE, RETURNED_DATE FROM JD11.BOOK
The view can now be selected against and updated as though it were a table. The extra columns in the BOOK table will not be available to users using the BOOKLEND view. You must be careful with constraints here, if a user wants to create a new BOOK row via BOOKLEND it might prove impossible if columns to which the user has no access need some value (have NOT NULL constraints). Views cannot contain ORDER BY clauses (but any SELECT statement run against the view can have an ORDER BY clause).
If we wanted to provide a list of the books by section without relying on a user to specify the SQL involved in the join we could provide this functionality in a complex view.
CREATE OR REPLACE VIEW BOOKBYSECT AS
SELECT A.TITLE BOOK_TITLE, B.SECTION_NAME SECTION FROM JD11.BOOK A, JD11.SECTION B WHERE A.SECTION_ID = B.SECTION_ID
The OR REPLACE option will force any view of the same name to be replaced with our new view. We can now query the new view much more simply than the original tables, however, we would not be able to update any data via the BOOKBYSECT view.
SELECT * FROM JD11.BOOKBYSECT ORDER BY SECTION, BOOK_TITLE
<><><><><><><>
It is possible to create views of tables that don't actually exist yet by using the FORCE option, this may be useful if database and application development are happening simultaneously.
CREATE FORCE VIEW BOOKBYSECT AS
SELECT A.TITLE BOOK_TITLE, B.SECTION_NAME SECTION FROM JD11.BOOK A, JD11.SECTION B WHERE A.SECTION_ID = B.SECTION_ID
If we hadn't created the BOOK and SECTION tables yet we could use the above command to create the view. You cannot SELECT from the view until the tables are created.
With a simple view we define the view with a SELECT command. If the select command has a WHERE clause that will define the rows that the user can see.
CREATE VIEW BOOKLEND AS SELECT ISBN, TITLE, LENT_DATE, RETURNED_DATE FROM JD11.BOOK WHERE ISBN > 100
The above view would only allow rows with ISBN above 100 to be displayed to a user, this doesn't stop the user updating all rows in the table even though they can't SELECT them (column and table constraints not withstanding). To cope with this we can use the CHECK OPTION clause, this stops a user updating rows that they can't SELECT via the view.
CREATE VIEW BOOKLEND AS SELECT ISBN, TITLE, LENT_DATE, RETURNED_DATE FROM JD11.BOOK WHERE ISBN > 100 WITH CHECK OPTION
You can remove a view by using the DROP command.
DROP VIEW BOOKLEND
They are normally used by the DBA (to a limited extent), a database designer or application developer. These statements are immediate, they are not susceptible to ROLLBACK commands.
You should also note that if you have executed several DML updates then issuing any DDL command will COMMIT all the updates as every DDL command implicitly issues a COMMIT command to the database.
Anybody using DDL must have the CREATE object privilege and a Tablespace area in which to create objects.
In an Oracle database objects can be created at any time, whether users are on-line or not. Table space need not be specified as Oracle will pick up the user defaults (defined by the DBA) or the system defaults.
Tables will expand automatically to fill disk partitions (provided this has been set up in advance by the DBA). Table structures may be modified on-line although this can have dire effects on an application so be careful.
Creating our two example tables:
CREATE TABLE BOOK (
ISBN NUMBER(10),
TITLE VARCHAR2(200),
AUTHOR VARCHAR2(50),
COST NUMBER(8,2),
LENT_DATE DATE,
RETURNED_DATE DATE,
TIMES_LENT NUMBER(6),
SECTION_ID NUMBER(3))
CREATE TABLE SECTION (
SECTION_ID NUMBER(3),
SECTION_NAME CHAR(30),
BOOK_COUNT NUMBER(6) )
The two commands above create our two sample tables and demonstrate the basic table creation command. The CREATE keyword is followed by the type of object that we want created (TABLE, VIEW, INDEX etc.), and that is followed by the name we want the object to be known by. Between the outer brackets lie the parameters for the creation, in this case the names, datatypes and sizes of each field.
A NUMBER is a numeric field, the size is not the maximum externally displayed number but the size of the internal binary field set aside for the field (10 can hold a very large number). A number size split with a comma denotes the field size followed by the number of digits following the decimal point (in this case a currency field has two significant digits)
A VARCHAR2 is a variable length string field from 0-n where n is the specified size. Oracle only takes up the space required to hold any value in the field, it doesn't allocate the entire storage space unless required to by a maximum sized field value (Max size 2000).
A CHAR is a fixed length string field (Max size 255).
A DATE is an internal date/time field (normally 7 bytes long).
A LONG or LONG RAW field (not shown) is used to hold large binary objects (Word documents, AVI files etc.). No size is specified for these field types. (Max size 2Gb).
Creating our two tables with constraints :
Constraints are used to enforce table rules and prevent data dependent deletion (enforce database integrity). You may also use them to enforce business rules (with some imagination).
Our two example tables do have some rules which need enforcing, specifically both tables need to have a prime key (so that the database doesn't allow replication of data). And the Section ID needs to be linked to each book to identify which library section it belongs to (the foreign key). We also want to specify which columns must be filled in and possibly some default values for other columns. Constraints can be at the column or table level.
Constraint | <><><><><><><> Description | <><><><><><><>
NULL / NOT NULL | <><><><><><><> NOT NULL specifies that a column must have some value. NULL (default) allows NULL values in the column. | <><><><><><><>
DEFAULT | <><><><><><><> Specifies some default value if no value entered by user. | <><><><><><><>
UNIQUE | <><><><><><><> Specifies that column(s) must have unique values | <><><><><><><>
PRIMARY KEY | <><><><><><><> Specifies that column(s) are the table prime key and must have unique values. Index is automatically generated for column. | <><><><><><><>
FOREIGN KEY | <><><><><><><> Specifies that column(s) are a table foreign key and will use referential uniqueness of parent table. Index is automatically generated for column. Foreign keys allow deletion cascades and table / business rule validation. | <><><><><><><>
CHECK | <><><><><><><> Applies a condition to an input column value. | <><><><><><><>
DISABLE | <><><><><><><> You may suffix DISABLE to any other constraint to make Oracle ignore the constraint, the constraint will still be available to applications / tools and you can enable the constraint later if required. | <><><><><><><>
CREATE TABLE SECTION ( SECTION_ID NUMBER(3) CONSTRAINT S_ID CHECK (SECTION_ID > 0),
SECTION_NAME CHAR(30) CONSTRAINT S_NAME NOT NULL, BOOK_COUNT NUMBER(6),
CONSTRAINT SECT_PRIME PRIMARY KEY (SECTION_ID))
CREATE TABLE BOOK ( ISBN NUMBER(10) CONSTRAINT B_ISBN CHECK (ISBN BETWEEN 1 AND 2000), TITLE VARCHAR2(200) CONSTRAINT B_TITLE NOT NULL, AUTHOR VARCHAR2(50) CONSTRAINT B_AUTH NOT NULL, COST NUMBER(8,2) DEFAULT 0.00 DISABLE, LENT_DATE DATE, RETURNED_DATE DATE, TIMES_LENT NUMBER(6), SECTION_ID NUMBER(3),
CONSTRAINT BOOK_PRIME PRIMARY KEY (ISBN),
CONSTRAINT BOOK_SECT FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID))
We have now created our tables with constraints.
Column level constraints go directly after the column definition to which they refer, table level constraints go after the last column definition. Table level constraints are normally used (and must be used) for compound (multi column) foreign and prime key definitions, the example table level constraints could have been placed as column definitions if that was your preference (there would have been no difference to their function).
The CONSTRAINT keyword is followed by a unique constraint name and then the constraint definition.
The constraint name is used to manipulate the constraint once the table has been created, you may omit the CONSTRAINT keyword and constraint name if you wish but you will then have no easy way of enabling / disabling the constraint without deleting the table and rebuilding it, Oracle does give default names to constraints not explicitly name - you can check these by selecting from the USER_CONSTRAINTS data dictionary view.
Note that the CHECK constraint implements any clause that would be valid in a SELECT WHERE clause (enclosed in brackets), any value inbound to this column would be validated before the table is updated and accepted / rejected via the CHECK clause. Note that the order that the tables are created in has changed, this is because we now reference the SECTION table from the BOOK table.
The SECTION table must exist before we create the BOOK table else we will receive an error when we try to create the BOOK table. The foreign key constraint cross references the field SECTION_ID in the BOOK table to the field (and primary key) SECTION_ID in the SECTION table (REFERENCES keyword).
If we wish we can introduce cascading validation and some constraint violation logging to our tables.
CREATE TABLE AUDIT ( ROWID ROWID, OWNER VARCHAR2,TABLE_NAME VARCHAR2, CONSTRAINT VARCHAR2)
CREATE TABLE SECTION (SECTION_ID NUMBER(3) CONSTRAINT S_ID CHECK (SECTION_ID > 0), SECTION_NAME CHAR(30) CONSTRAINT S_NAME NOT NULL, BOOK_COUNT NUMBER(6), CONSTRAINT SECT_PRIME PRIMARY KEY (SECTION_ID),EXCEPTIONS INTO AUDIT)
CREATE TABLE BOOK ( ISBN NUMBER(10) CONSTRAINT B_ISBN CHECK (ISBN BETWEEN 1 AND 2000),
TITLE VARCHAR2(200) CONSTRAINT B_TITLE NOT NULL, AUTHOR VARCHAR2(50) CONSTRAINT B_AUTH NOT NULL, COST NUMBER(8,2) DEFAULT 0.00 DISABLE, LENT_DATE DATE, RETURNED_DATE DATE, TIMES_LENT NUMBER(6), SECTION_ID NUMBER(3),CONSTRAINT BOOK_PRIME PRIMARY KEY (ISBN),CONSTRAINT BOOK_SECT FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID)ON DELETE CASCADE)
Oracle (and any other decent RDBMS) would not allow us to delete a section which had books assigned to it as this breaks integrity rules. If we wanted to get rid of all the book records assigned to a particular section when that section was deleted we could implement a DELETE CASCADE. The delete cascade operates across a foreign key link and removes all child records associated with a parent record (we would probably want to reassign the books rather than delete them in the real world).
To log constraint violations I have created a new table (AUDIT) and stated that all exceptions on the SECTION table should be logged in this table, you can then view the contents of this table with standard SELECT statements. The AUDIT table must have the shown structure but can be called anything.
It is possible to record a description or comment against a newly created or existing table or individual column by using the COMMENT command. The comment command writes your table / column description into the data dictionary. You can query column comments by selecting against dictionary views ALL_COL_COMMENTS and USER_COL_COMMENTS.
You can query table comments by selecting against dictionary views ALL_TAB_COMMENTS and USER_TAB_COMMENTS. Comments can be up to 255 characters long, examples are given below :
COMMENT ON TABLE JD11.BOOK IS 'Library Book Information'
COMMENT ON
COLUMN JD11.BOOK.AUTHOR IS 'Authors Name'
Altering tables and constraints :
Modification of database object structure is executed with the ALTER statement.
You can modify a constraint as follows :-
Add new constraint to column or table.
Remove constraint.
Enable / disable constraint.
You cannot change a constraint definition.
You can modify a table as follows :-
Add new columns.
Modify existing columns.
You cannot delete an existing column.
An example of adding a column to a table is given below :
ALTER TABLE JD11.BOOK ADD (REVIEW VARCHAR2(200))
This statement adds a new column (REVIEW) to our book table, to enable library members to browse the database and read short reviews of the books.
If we want to add a constraint to our new column we can use the following ALTER statement :
ALTER TABLE JD11.BOOK MODIFY(REVIEW NOT NULL)
Note that we can't specify a constraint name with the above statement. If we wanted to further modify a constraint (other than enable / disable) we would have to drop the constraint and then re apply it specifying any changes.
Assuming that we decide that 200 bytes is insufficient for our review field we might then want to increase its size. The statement below demonstrates this :
ALTER TABLE JD11.BOOK MODIFY (REVIEW VARCHAR2(400))
We could not decrease the size of the column if the REVIEW column contained any data.
ALTER TABLE JD11.BOOK DISABLE CONSTRAINT B_AUTH
ALTER TABLE JD11.BOOK ENABLE CONSTRAINT B_AUTH
The above statements demonstrate disabling and enabling a constraint, note that if, between disabling a constraint and re enabling it, data was entered to the table that included NULL values in the AUTHOR column, then you wouldn't be able to re enable the constraint. This is because the existing data would break the constraint integrity. You could update the column to replace NULL values with some default and then re enable the constraint.
Dropping (deleting) tables and constraints :
To drop a constraint from a table we use the ALTER statement with a DROP clause. Some examples follow :
ALTER TABLE JD11.BOOK DROP CONSTRAINT B_AUTH
The above statement will remove the not null constraint (defined at table creation) from the AUTHOR column. The value following the CONSTRAINT keyword is the name of constraint.
ALTER TABLE JD11.BOOK DROP PRIMARY KEY
The above statement drops the primary key constraint on the BOOK table.
ALTER TABLE JD11.SECTION DROP PRIMARY KEY CASCADE
The above statement drops the primary key on the SECTION table. The CASCADE option drops the foreign key constraint on the BOOK table at the same time.
Use the DROP command to delete database structures like tables. Dropping a table removes the structure, data, privileges, views and synonyms associated with the table (you cannot rollback the DROP so be careful). You can specify a CASCADE option to ensure that constraints refering to the dropped table within other tables (foreign keys) are also removed by the DROP.
DROP TABLE SECTION
The above statement drops the table SECTION but leaves the foreign key reference within the BOOK table.
DROP TABLE SECTION CASCADE CONSTRAINTS
The above statement drops the table SECTION and removes the foreign key reference from the BOOK table.
Renaming Database Objects :
Use the RENAME command to change the name of tables, views and synonyms. Note that if you change an object's name you will have to update any references to it in any application or other database object that uses it. It is wise to decide on an object's name then stick with it, even if you don't like the name you will probably save yourself a lot of work if you just live with it.
RENAME JD11.BOOK TO JD11.LIBRARY_BOOK
Deleting all data from a table :
You can delete all the data in a table by using the TRUNCATE command (you can also achieve this with a DELETE command but it is not as efficient due to the ROLLBACK memory usage). TRUNCATE flushes the table of data but leaves the structure and constraints intact. You can add a REUSE STORAGE option which tells the table to hold onto the memory it used to store the rows (but I can't think of any reason why you would want to do this).
TRUNCATE TABLE BOOK
TRUNCATE TABLE BOOK REUSE STORAGE
Indexes :
Indexes are used to speed up row retrieval and enforce column uniqueness. Oracle automatically creates an index on any key column or any column with the UNIQUE constraint. Oracle stores indexes in index segments attached to the user's default tablespace, you may attach the index segment to another tablespace when you create an index if you think this will help performance.
Oracle normally uses balanced binary tree index structures as these give roughly equal access times to all table rows, each index consists of a number of pages of storage arranged in a hierarchical tree. Each page holds a series of key values and pointers to pages lower in the structure until eventually the keys point to the data rows themselves.
There are four index types, they are :-
UNIQUE - ensures that values in specified column(s) are unique.
NON UNIQUE - Ensures fastest possible results when querying / ordering data.
SINGLE COLUMN - Only a single column makes up the index.
CONCATENATED - Up to 16 columns make up the index.
Indexes can be created on-line, an example follows :
CREATE INDEX IDX_SECT_SECT ON JD11.SECTION(SECTION_NAME)
This index will speed up SELECT statements using the section name as the WHERE clause parameter.
CREATE UNIQUE INDEX IDX_SECT_SECT ON JD11.SECTION(SECTION_NAME)
This index will speed up SELECT statements using the section name in the WHERE clause and will force the SECTION_NAME column to contain distinctive values.
DROP INDEX IDX_SECT_SECT
The above statement removes the index.
I will not go through all the ins and outs of indexes here but here are a few important points.
For a SELECT statement to use an index it must have a WHERE clause which keys on the index column where the index column is not part of a function (like upper casing etc.).
For compound WHERE clauses the index column must be the first column keyed upon.
Ensure that joined tables have indexes on any columns that are used in the join, this can save a lot of time during execution because otherwise Oracle must individually sort each table, then merge the sorted lists together (this is very slow on large tables).
Index any column where unique values are required.
Index any column frequently used in WHERE clauses.
If two or more columns are frequently used in WHERE or JOIN conditions then create a CONCATENATED index with them.
Avoid overusing indexes - there is an operational overhead for each index.
Sequences :
A sequence generator is a construct used to generate sequential unique numbers, it is usually used to produce table key values where the table data has no clearly identifiable prime key or where the identification of rows within a table would involve a large, unwieldy compound primary key.
It is a shareable database object and its owner can grant access to it to other database users. This construct provides a similar function to (but much more flexibility than) the MS Access auto number. A sequence creation statement can have several parameters, the possibilities are listed below.
INCREMENT BY | <><><><><><><> The interval value between sequence numbers. | <><><><><><><>
START WITH | <><><><><><><> The starting value of the sequence. | <><><><><><><>
MINVALUE / NOMINVALUE | <><><><><><><> The minimum sequence number. | <><><><><><><>
MAXVALUE / NOMAXVALUE | <><><><><><><> The maximum sequence number. | <><><><><><><>
CACHE / NOCACHE | <><><><><><><> How many values are kept in the cache - for performance reasons large databases using sequences very frequently will want a lot of values cached. | <><><><><><><>
CYCLE / NOCYCLE | <><><><><><><> Specifies whether the sequence generator will cycle back to MINVALUE from MAXVALUE when MAXVALUE is met. For sequences providing key values it is probably not a good idea to cycle back. | <><><><><><><>
We will now create a sequence to be used with our SECTION table, it will be used to generate the unique section numbers.
CREATE SEQUENCE SECIDNUM INCREMENT BY 1 START WITH 1 MAXVALUE 100 NOCACHE.
The value following the SEQUENCE key word is the name of the sequence generator, you will use this from now on to refer to the sequence. To get values from the sequence we use a SELECT statement like the one below, obviously this select would form part of the application creation event for a new library section.
SELECT SECIDNUM.NEXTVAL FROM SYS.DUAL
This select gets the next sequential number from the generator and updates the sequence with the INCREMENT BY value. Note that if you don't use the value it is thrown away, the sequence number does not guarantee uninterrupted number sequences (this might be an audit issue, especially on financial systems). The NEXTVAL keyword tells the generator to provide the next sequential value. You may replace NEXTVAL with CURRVAL to look at the current value of a sequence.
If we wanted to use the sequence to create a new library section in SQL *Plus rather than an application we could use a statement like this.
INSERT INTO JD11.SECTION VALUES (SECIDNUM.NEXTVAL, 'Computing', 0)
You may alter the INCREMENT BY, MINVALUE and MAXVALUE values of a sequence by using the ALTER command.
ALTER SEQUENCE SECIDNUM INCREMENT BY 2
ALTER SEQUENCE SECIDNUM MINVALUE 10
ALTER SEQUENCE SECIDNUM MAXVALUE 2500
You can remove a sequence by using the DROP command.
DROP SEQUENCE SECIDNUM
Views :
A view is a method of organising table data to meet a specific need. Views are based on SELECT statements which derive their data from real tables. A view allows you to reorganise the database data, you might want to do this so that you can restrict data access, reduce selection complexity, provide improved data independence or allow disparate users to view the same data in different ways. Most of the Oracle data dictionary is readable via views which interpret internal Oracle tables. Views come in simple and complex forms. Simple views are based on a single table and data can be updated via DML commands (privilege issues aside), complex views are derived from multiple tables and DML commands cannot be used to update data.
If we wanted our front desk librarians to only see lending data from the BOOK table we could create a view that fulfilled this need.
CREATE VIEW BOOKLEND AS
SELECT ISBN, TITLE, LENT_DATE, RETURNED_DATE FROM JD11.BOOK
The view can now be selected against and updated as though it were a table. The extra columns in the BOOK table will not be available to users using the BOOKLEND view. You must be careful with constraints here, if a user wants to create a new BOOK row via BOOKLEND it might prove impossible if columns to which the user has no access need some value (have NOT NULL constraints). Views cannot contain ORDER BY clauses (but any SELECT statement run against the view can have an ORDER BY clause).
If we wanted to provide a list of the books by section without relying on a user to specify the SQL involved in the join we could provide this functionality in a complex view.
CREATE OR REPLACE VIEW BOOKBYSECT AS
SELECT A.TITLE BOOK_TITLE, B.SECTION_NAME SECTION FROM JD11.BOOK A, JD11.SECTION B WHERE A.SECTION_ID = B.SECTION_ID
The OR REPLACE option will force any view of the same name to be replaced with our new view. We can now query the new view much more simply than the original tables, however, we would not be able to update any data via the BOOKBYSECT view.
SELECT * FROM JD11.BOOKBYSECT ORDER BY SECTION, BOOK_TITLE
Book_tile | <><><><><><><> Section | <><><><><><><>
Over the past again | <><><><><><><> Fiction | <><><><><><><>
Up the creek | <><><><><><><> Fiction | <><><><><><><>
Leaning on a tree | <><><><><><><> Law | <><><><><><><>
Courses for horses | <><><><><><><> Reference | <><><><><><><>
Dirty steam trains | <><><><><><><> Reference | <><><><><><><>
HELP | <><><><><><><> Reference | <><><><><><><>
Killer Bees | <><><><><><><> Reference | <><><><><><><>
Seven seas | <><><><><><><> Romance | <><><><><><><>
The story of trent | <><><><><><><> Science Fiction | <><><><><><><>
It is possible to create views of tables that don't actually exist yet by using the FORCE option, this may be useful if database and application development are happening simultaneously.
CREATE FORCE VIEW BOOKBYSECT AS
SELECT A.TITLE BOOK_TITLE, B.SECTION_NAME SECTION FROM JD11.BOOK A, JD11.SECTION B WHERE A.SECTION_ID = B.SECTION_ID
If we hadn't created the BOOK and SECTION tables yet we could use the above command to create the view. You cannot SELECT from the view until the tables are created.
With a simple view we define the view with a SELECT command. If the select command has a WHERE clause that will define the rows that the user can see.
CREATE VIEW BOOKLEND AS SELECT ISBN, TITLE, LENT_DATE, RETURNED_DATE FROM JD11.BOOK WHERE ISBN > 100
The above view would only allow rows with ISBN above 100 to be displayed to a user, this doesn't stop the user updating all rows in the table even though they can't SELECT them (column and table constraints not withstanding). To cope with this we can use the CHECK OPTION clause, this stops a user updating rows that they can't SELECT via the view.
CREATE VIEW BOOKLEND AS SELECT ISBN, TITLE, LENT_DATE, RETURNED_DATE FROM JD11.BOOK WHERE ISBN > 100 WITH CHECK OPTION
You can remove a view by using the DROP command.
DROP VIEW BOOKLEND
No comments:
Post a Comment