Creating and Modifying Database Tables
Data in relational database tables are inserted, retrieved, modified and deleted using commands called queries.
Queries are usually described by a language called SQL (which stands for Standard Query Language). [note: SQL is pronounced 'Sequel' to rhyme with equal]
SQL is clearly the standard when it comes to relational databases. Just about every database server supports it.
It includes features for defining the structure of the data (DDL), for modifying data in the database, for specifying security constraints, and obviously, to query (to retrieve) data from a database.
There are many flavors of SQL. Every database seems to have its own slightly different version from everybody else. So, while the SQL basics may be compatible with all databases, there will almost always be a few obscure features which are only supported under one vendor (for example, some database support nested SQL statements, while others don't).
SQL language has several parts:
Data Definition Language (DDL): provides commands for defining relation schemas, deleting relations, creating indices, and modifying relation schemas.
Interactive Data Manipulation Language (DML): provides command to insert, modify and delete tuples in the database.
Embedded DML: The embedded form of SQL designed for use within general-purpose programming languages like PL/I, COBOL, Pascal, Fortran, and C.
View Definition: DDL includes commands for defining views.
Authorization: DDL includes commands for specifying access rights to relations and views.
Integrity: DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.
Transaction Control: SQL includes commands for specifying the beginning and end of transactions. Several implementations also allow explicit locking of data for concurrency control.
Note that not all of these features are present in all databases. Some databases have no transactions, views, etc., like MySQL. In general, it doesn't make the database less useful, it just makes it useful for different things (things that don't require transactions or views). Also, usually, the less extra features a database has, the faster and more efficient it is (like MySQL).
Tables - (or as some references call them: Relations)
The first task of developing a database is to create the database tables.
From a physical point of view, databases are just a collection of files. [Oracle Specific] From a local point of view, a database is a set of user accounts. Each user account owns tables and other data objects. Within an individual user account, table names have to be unique.
When you create a table, you must specify the table name, the name of each data field, the data type and size of each field, and possibly some constraints that specify whether the field is a primary key, whether it is a foreign key, whether NULL value is allowed and/or whether data are restricted to certain values.
Table names can be from one to 30 characters long and can consist of only alphanumeric characters and special characters $, _ and #. Table and field names must begin with a letter and cannot contain blank spaces or hyphens.
For example, "last-name" is an invalid field.
When you create a table, you must assign each column a data type. This specifies that kind of data will be stored in that field.
Data types serve several purposes:
Error checking: You can't store wrong time data. For example, you can't store "john doe" in a DATE field.
Space saving: The database can use data types to determine the most efficient use of space. For example, the database will not store DATEs as text strings; but will use a more compact binary format.
Some Important Types
VARCHAR2: Stores a variable length string of max 4000 characters. Does not store extra spaces at the end of the string, for example,
CHAR: Stores a string of max size 255. Expands strings to the CHAR size (fills with zeroes). An example use in creating a table would be:
LONG: This is not a numeric type. This is a long variable length character data field. Well suited for storing entire text files.
NUMBER: Numeric type. Without any size, capable of holding a floating point number from 10-130 to 10126 with precision up to 38 decimal places.
NUMBER(N): Defines an integer value with N digits.
NUMBER(N,M): Defines a fixed point number, with N whole number digits, and M decimal places.
DATE: Date that can store dates from January 1, 4712 BC to December 31 4712 AD. Stores date and time.
Integrity constraints allow us to define primary keys and foreign keys on tables (ensuring that tables maintain their relationship).
To define a PRIMARY KEY constraint, you add this to the column declaration:
CONSTRAINT constraintname PRIMARY KEY
To define a foreign key constraint, you add this to the column declaration:
CONSTRAINT constraintname REFERENCES tablewherefiedisprimarykey ( nameoffieldwherefieldisprimarykey )
It is recommended that you pick names for constraints (if you don't, the db will come up with default ones, but it's a good practice to create your own).
For naming, you can use: tablename_fieldname_NN, where NN is: pk for primary key, fk for foreign key, cc for check condition, and nn for not null.
Value constraints allow you ensure that only valid values are allowed to be stored in a column. For example, you can ensure that some value is say between 250 and 500 by setting a constraint like this:
somefname NUMBER(3) CONSTRAINT somefname_cc CHECK ((somefname > 250) AND (somefname < 500))
Default value constraint
There is a default value constraint:
fieldname fieldtype DEFAULT value
The above code would set the default value of 'AB' if you don't insert anything into that column.
Create Table Examples
Oracle maintains special tables to hold metadata, which you sometimes may need to look at.
User Table Information
To list all of user tables, simply issue:
SELECT table_name FROM user_tables;
You can then use:
To get details about any particular table.
To get a listing of all constraints, you can do this:
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'SOMETHING'
You can modify tables. Some things you can't modify easily, while others are fairly easy. For example, if you're modifying a constraint, you will most likely need to drop the current constraint.
You also cannot rename or delete columns. You can however add columns, delete a primary key or foreign key constraint, etc.
Renaming a Table
To rename a table use: RENAME oldname newname.
Adding Fields to Existing Tables
To add a field to a table:
Modifying Existing Fields
You can modify an already existing column (to some degree).
ALTER TABLE tablename MODIFY fieldname newdatadeclaration;
ALTER TABLE tablename DROP CONSTRAINT constraintname;
Adding (& Dropping) Constraints to Existing Tables
To add a constraint to an existing table we can use the ALTER TABLE command. The general format for that is:
For example, if you're adding a foreign key constraint, you'd do something like this:
Composite Primary Keys
If a table doesn't have an obvious single primary key we can combine several columns to form a composite primary key. These behave just like a single primary key, except that the combination has to be unique.
We can add such a key by specifying several columns in PRIMARY KEY(...) command when creating a table. For example:
This would make the combination of A, and B the primary key for table FOO.
We can also name the primary key by specifying: