Skip to main content

Posts

Showing posts from April, 2011

Basics of RDBMS

Data
Small set of information becomes data, this set of information helps make decision. Data is always some useful information.


Database
Place where you store the data. Database represents some aspect of the real world called "miniworld". A database is designed, built and populated with data for a specific purpose. It has intended group of users and some preconceived applications in which these users are interested.

In other words, a database has some source from which data is derived, some degree of interaction with events in the real world and an audience that is actively interested in the contents of the database.

Database can also be defined as collection of one or more tables.

Ex: Mobile, human brain etc



DBMS (Database Management System)
Is a program that stores retrieves and modifies data in the database on request.

Study of different techniques of design, development and maintenance of the database

Types of DBMS
These types are based upon their management of database s…

Query to find column constraints Primary Key, Foreign Key, Check, Not Null and Unique

It is always a question that how do I know what constraints are there on each column of a table. Following query gives access to this information,

SELECT a.table_name,a.column_name,a.constraint_name, b.constraint_type, b.search_condition FROM USER_CONS_COLUMNS a, USER_CONSTRAINTS b WHERE a.constraint_name = b.constraint_name ORDER BY a.table_name;

Above query returns all the tables and all its columns with some constraints.

CONSTRAINT_TYPE column in table USER_CONSTRAINTS store information about what type of constraint a column is. The mapping goes this way,

1. C --> Check/Not Null
2. P --> Primary Key
3. R --> Foreign Key
4. U --> Unique

Since CONSTRAINT_TYPE defines both CHECK and NOT NULL as C, Column SEARCH_CONDITION gives more details on CONSTRAINT_TYPE C.


If you are only interested only in knowing NOT NULL columns for a table then just use DESCRIBE <tablename>. This lists columns with not null constraint.


Sample output of the above query on my database is as below…