Notes 0002

Creating and Modifying Database Tables

Introduction

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

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.

For example:

CREATE TABLE tablename (
 ...
);

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.

Data Types

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, "john " will be truncated to "john". An example use in creating a table would be:

lastname VARCHAR2(20)

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:

lastname CHAR(20)

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 10¹²⁶ 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

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 )

Naming Convention

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

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

For example:

CREATE TABLE sometable (
   someid INT,
   somestatuscode CHAR(2) DEFAULT 'AB'
);

The above code would set the default value of 'AB' if you don't insert anything into that column.

Create Table Examples

CREATE TABLE product (
   product_id INT 
      CONSTRAINT product_product_id_pk PRIMARY KEY,
   description VARCHAR(128) 
      CONSTRAINT product_description_nn NOT NULL,
   quantity INT 
      CONSTRAINT product_quantity_nn NOT NULL,
   price NUMBER(6,2) 
      CONSTRAINT product_price_nn NOT NULL
);


insert into product values (3,'mice',20,26.99);
insert into product values (4,'keyboard',90,19.95);


CREATE TABLE productorder (
   order_id INT 
      CONSTRAINT productorder_order_id_nn NOT NULL,
   product_id INT 
      CONSTRAINT productorder_product_id_fk 
         REFERENCES product(product_id) 
      CONSTRAINT productorder_product_id_nn NOT NULL,
   quantity INT CONSTRAINT productorder_quantity_nn NOT NULL
);


insert into productorder values (1,3,2);
insert into productorder values (1,4,2);
insert into productorder values (2,3,5);
insert into productorder values (3,4,3);

Special Tables

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:

DESCRIBE tablename

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'

Modifying Tables

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:

ALTER TABLE tablename
ADD fieldname datatype 
CONSTRAINT integrity constraint
CONSTRAINT value constraints

Modifying Existing Fields

You can modify an already existing column (to some degree).

ALTER TABLE tablename MODIFY fieldname newdatadeclaration;

Dropping Constraints

ALTER TABLE tablename DROP CONSTRAINT constraintname;

Dropping Tables

DROP tablename

UPDATED

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:

ALTER TABLE tablename
  ADD attributename datatype constraint
  ADD tableconstraint
  MODIFY attributename datatype constraint
  DROP PRIMARY KEY
  DROP UNIQUE attributename  (drops the unique constraint on an attribute)
  DROP CONSTRAINT constraintname

ALTER TABLE Patron
  DROP CONSTRAINT nn_name

For example, if you're adding a foreign key constraint, you'd do something like this:

ALTER TABLE tablename
ADD CONSTRAINT comstraintname
FOREIGN KEY (columnname_of_foreign_key)
REFERENCES table_to_which_fk(column_to_which_fk);

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:

CREATE TABLE FOO (
    A INT,
    B INT,
    PRIMARY KEY(A,B)
);

This would make the combination of A, and B the primary key for table FOO.

We can also name the primary key by specifying:

CREATE TABLE FOO (
    A INT,
    B INT,
    CONSTRAINT foo_pk PRIMARY KEY(A,B)
);