FIVE 5 constraints available in ORACLE DBMS LAB FOR ME STUDENTS - C C++ Java Programs - Examples

Latest

C C++ Java Python Perl Programs Examples with Output -useful for Schools & College Students

Wednesday, October 12, 2011

FIVE 5 constraints available in ORACLE DBMS LAB FOR ME STUDENTS


FIVE 5 constraints available in ORACLE:

1. NOT NULL: When a column is defined as NOTNULL, then that column becomes a mandatory column. It implies that a value must be entered into the column if the record is to be accepted for storage in the table.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) NOT NULL, );
Example:
CREATE TABLE student (sno NUMBER(3)NOT NULL, name CHAR(10));
2. UNIQUE: The purpose of a unique key is to ensure that information in the column(s) is unique i.e. a value entered in column(s) defined in the unique constraint must not be repeated across the column(s). A table may have many unique keys.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) UNIQUE, ….);
Example:
CREATE TABLE student (sno NUMBER(3) UNIQUE, name CHAR(10));

3. CHECK: Specifies a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null).
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) CHECK(logical expression), ….);
Example: CREATE TABLE student (sno NUMBER (3), name CHAR(10),class CHAR(5),CHECK(class IN(‘CSE’,’CAD’,’VLSI’));

4. PRIMARY KEY: A field which is used to identify a record uniquely. A column or combination of columns can be created as primary key, which can be used as a reference from other tables. A table contains primary key is known as Master Table.
ü  It must uniquely identify each record in a table.
ü  It must contain unique values.
ü  It cannot be a null field.
ü  It cannot be multi port field.
ü  It should contain a minimum no. of fields necessary to be called unique.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size) PRIMARY KEY, ….);
Example:
CREATE TABLE faculty (fcode NUMBER(3) PRIMARY KEY, fname CHAR(10));

5. FOREIGN KEY: It is a table level constraint. We cannot add this at column level. To reference any primary key column from other table this constraint can be used. The table in which the foreign key is defined is called a detail table. The table that defines the primary key and is referenced by the foreign key is called the master table.
Syntax:    CREATE TABLE Table_Name(column_name data_type(size)
FOREIGN KEY(column_name) REFERENCES table_name);
Example:
CREATE TABLE subject (scode NUMBER (3) PRIMARY KEY,
subname CHAR(10),fcode NUMBER(3),
FOREIGN KEY(fcode) REFERENCE faculty );

No comments:

Post a Comment