Declaration Style:
-------------------
Constraint can be created in two levels
1) Column Level
2) Table Level
Column Level:
---------------
They are declared as part of the definition of the individual column.
Table Level:
----------------
They are declared as part of the table definition.
1) NOT NULL Constraint:
------------------------
NOT NULL constraint does not accept NULL values.
To satisfy this constraint, every row in the table must contain a value.
NOT NULL constraint can be created only at column level.
Create table student1 ( sno number(3) NOT NULL,
sname varchar2(10),
marks number(3));
insert into student1 values ( 101,'arun', 40); -- valid
insert into student1 values ( 102,'kiran', 75); -- valid
insert into student1 values ( null,'arun', 40); -- error
UNIQUE COnstraint:
-----------------------
UNIQUE constraint does not accept duplicate value.
UNIQUE constraint can be created at column level or at table level.
UNIQUE constraint will accept NULL value.
Syntax of UNIQUE constraint at column level:
----------------------------------------------
insert into student3 values ( 101, 'arun',40); -- valid
insert into student3 values ( 102, 'kiran',75); -- valid
insert into student3 values ( 101, 'vijay',55); -- error
insert into student3 values ( null,'ashok',85); -- valid
insert into student3 values ( null,'vinay',81); -- valid
There is no difference practically , when a constraint is created at column level
or table level. You can follow any syntax.
PRIMARY KEY:
------------------
A PRIMARY KEY constraint is combination of NOT NULL and UNIQUE constraint.
A PRIMARY KEY column will not accept NULL values and DUPLICATE values.
A PRIMARY KEY column is used to uniquely identify every row in the table.
A Table can have only one PRIMARY KEY
A PRIMARY KEY Constraint can be created at column level or at table level.
Syntax of PRIMARY KEY at Column Level:
-----------------------------------------
A Foreign key constraint establishes relationship between tables.
This relationship is called as Parent-child relationship.
It is also called as Master-Detail relationship.
A Foreign Key column in child table will only accept the values present in the Primary Key or Unique column of the parent table.
A foreign key constraint can be created at column level or at table level.
TO understand this FOREIGN KEY Constraint , have a look at this scenario:
-------------------------------------------------------------------------------
I have started a school.
Assume only 3 students joined in my school.
I create a table with name school and enter the student details in it. Every student will have student no. So, sno is primary Key
SCHOOL
Sno
Sname
Gender
Age
101
arun
M
5
102
kiran
M
5
103
sreeja
F
4
I have appointed librarian for my school. If any student borrows book, librarian should enter the sno and book name .
I create a table with name library . Every sunday i visit my school to check whether it is running properly or not, surprisingly , i have the following data in my library table.
Library
sno
book_name
102
oracle
103
java
103
c++
108
unix
If you observe, there is no student 108 in our school.
So the last row is invalid row. Probably this could be data entry mistake.
I do not want these kind of inavlid data into my library table.
That means, i should create library table in such a way thay it should accept only the sno values which are in school table.
So, we should create a relationship between these table. This relationship is called as parent-child relationship.
In our scenario,
School is the parent table.
Library is the child table.
sno column in the library table is called as FOREIGN KEY column.
Note: A Foreign key column in the child table will only accept the values present in the PRIMARY KEY column of the parent table.
Lets practically workout this scenario
---------------------------------------------------
Step 1: Creating the parent table
Step 2: Inserting rows into the parent table
Step 3: Creating child table which contains FOREIGN KEY
Step 4: Inserting rows into the child table and understand the behaviour of the FOREIGN KEY column
Step 1: Creating parent table
-----------------------------------------
create table school ( sno number(3) PRIMARY KEY,
sname varchar2(10),
gender varchar2(10),
age number(2));
Step 2: Inserting rows into the parent table
-----------------------------------------------------------
insert into school values ( 101, 'arun', 'M' , 5);
insert into school values ( 102, 'kiran', 'M' , 5);
insert into school values ( 103, 'sreeja', 'M' , 4);
Step 4: Insertingrow into the child table
----------------------------------------------------
insert into library values ( 102, 'oracle'); -- valid
insert into library values ( 103, 'java'); -- valid
insert into library values ( 103, 'c++'); -- valid
insert into library values ( 108, 'unix'); -- error
Note:
As we have established relationship, library table is not accepting sno 108.
This relationship helps in improving the accuracy of the database.
++++++++++++++++++++++++
Syntaxof Foreign key constraint at table level
create table <table_name> ( col_name1 datatype(size),
col_name2 datatype(size),
FOREIGN KEY ( col_name1) REFERENCES < parent table> ( primary key col name ) );