DBMS Training & SQL Tutorial :
Creating Database Objects

	apid	Theory you need to know!
	eview

Types of Database Tables :

The data in a permanent table persists after the session expires however the data in a temporary table is available only in the session that populated it. Temporary tables are generally used to store large intermediate calculations.

Global temporary tables can be accessed by all the programs in that session. However, the program that creates a local temporary table has exclusive access to it.

The ON COMMIT clause on temporary tables specifies that the records in the table are deleted when the transaction completes.

Column, Table & Referential Integrity Constraints :

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, REFERENCES and CHECK are the valid column constraints.
UNIQUE, CHECK, PRIMARY KEY and FORIEGN KEY are the valid table constraints.
Table constraints can be applied to individual columns and groups of columns.
NO ACTION, CASCADE and SET NULL are the valid referential integrity optional clauses for ON DELETE.
NO ACTION, CASCADE, SET NULL and SET DEFAULT are the valid referential integrity constraint optional clauses for ON UPDATE.
The CASCADE reverential integrity constraint optional clause performs the same changes to the foreign key as were made to the parent.
DEFERRABLE constraints are checked after every DELETE, UPDATE and INSERT statement or after a transaction.

Creating Database Tables :

Tables can be created through the CREATE TABLE <tablename> SQL statement.
The tables with the primary keys must be created before the tables that have foreign keys referencing that primary key. By following this method, we can avoid the use of the ALTER TABLE statement to add the constraints after the table is created.
A one-to-many relationship between two tables can be implemented by having a foreign key in one table referring to the primary key of the other.
A many-to-many relationship between two tables can be implemented by creating an intermediate table that has foreign keys referring to the two tables.
In the CREATE TABLE statement, all constraints that are declared using the CONSTRAINT paramenter must be given a name.