-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
Constraints Constraints are rules appled on tables. Types of Constraints: Declaration Style: Column Level: Table Level:
1) NOT NULL Constraint: Syntax: Create table < table_name> ( col_name1 datatype(size) NOT NULL, Ex: Create table student1 ( sno number(3) NOT NULL, insert into student1 values ( 101,'arun', 40); -- valid UNIQUE COnstraint: UNIQUE constraint does not accept duplicate value. Syntax of UNIQUE constraint at column level: Create table < table_name> ( col_name1 datatype(size) UNIQUE, Ex: Create table student2 ( sno number(3) UNIQUE, insert into student2 values ( 101, 'arun',40); -- valid Note: Syntax of UNIQUE constraint at table level: Create table < table_name> ( col_name1 datatype(size), Ex: insert into student3 values ( 101, 'arun',40); -- valid There is no difference practically , when a constraint is created at column level PRIMARY KEY: A PRIMARY KEY constraint is combination of NOT NULL and UNIQUE constraint. Syntax of PRIMARY KEY at Column Level: Create table < table_name> ( col_name1 datatype(size) PRIMARY KEY, Ex: Create table student4 ( sno number(3) PRIMARY KEY, insert into student4 values ( 101, 'arun',40); -- valid Syntax of PRIMARY KEY at table level: Create table < table_name> ( col_name1 datatype(size), Ex: insert into student5 values ( 101, 'arun',40); -- valid There is no difference practically , when a constraint is created at column level FOREIGN KEY Constraint: A Foreign key constraint establishes relationship between tables. TO understand this FOREIGN KEY Constraint , have a look at this scenario: I have started a school.
I have appointed librarian for my school. If any student borrows book, librarian should enter the sno and book name .
If you observe, there is no student 108 in our school. In our scenario,
Step 1: Creating parent table Step 2: Inserting rows into the parent table Ex: Step 4: Insertingrow into the child table Ex: Note: CHECK Constraint: Insert into student6 values ( 101,’arun’, 60); |