Syvum Home Page

Home > Quiz Games > Information Technology & Computer Programming > DBMS Training & SQL Tutorial > Print Preview

DBMS Training & SQL Tutorial :
Creating Database Objects

apid Theory you need to know!
eview

 

Types of Database Tables :

  • Database tables can be classified into Permanent and Temporary 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.

  • Temporary tables are further classified as Global and Local.

  • 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.


  • TRY EXERCISES ON ABOVE THEORY

 
7 more pages in DBMS Training & SQL Tutorial

Contact Info © 1999-2017 Syvum Technologies Inc. Privacy Policy Disclaimer and Copyright