Constraints
Constraint is the mechanism which is used to restrict the invalid data which is enters by the end user by implementing business rules we can apply the constraints on two situation on the table.
- During the creation of the table.
- After creation of the table.
Constraints have two names
- System defined Constraints names
- User defined constraints names
1. System defined Constraint names
A constraint name which is defined by the system internally those constraints can be called as system defined constraint names. System defined constraint names are always in the format of SYS_CN. Here n represents any no.
2. User defined Constraints
The constraint name which is defined by the user manually those means those names can be called as user defined constraint names. We can apply constraints in two levels.
- Column level constraints
- Table level constraints
1. Column level Constraints
Applying the constraints after defining the column immediately those constraints can be called as column level constraints.
2. Table level constraints
Applying the constraints after defining all the columns in the table or at the end of the table those constraints can be called as table level constraints. Constraints are classified into six types.
- Unique
- Not null
- Primary key
- Check
- Foreign key
- Default
Unique constraint
- This constraint doesn’t allow us to enter duplicate values of a particular column in the table.
- We can apply constraint more than one column in the table.
- We can apply this constraint more than one column in the same table.
- This constraint allows us to enter null values.
1. Unique key
Unique key is a constraint. It never allows duplicate values. But it allows null values. We can give constraint in two levels
- Table level constraints.
- Column level constraints.
Example for Column level constraints
create table work1 (empno number(10) unique, ename varchar(10), sal number(10));
Example for Table level constraints
create table work2(empno number(10),ename varchar(10), sal number(10), unique(empno));
2. Not Null
Not null is a constraint which never allows null values but allows duplicates. We can apply this constraint in column level only.
Column level Example
create table work3 (empno number(10) not null, ename varchar(10), sal number(10));
Table level Example
create table work4 (empno number(10) ,ename varchar(10),sal number(10) not null);
3. Primary key
It never allows duplicates and null values.
Column level
create table work6 (empno number(10) primary key,ename varchar(10),sal number(10));
Table level
create table work7 (empno number(10),ename varchar(10),sal number(10), primary key(empno));
4. Check
Which is used to check particular constraint.
create table work8 (empno number(10),ename varchar(10),sal number(10) check (sal=3000);
5. Foreign key Constraint
Using this constraint we can maintain the relation between the tables with in the database. This constraint can also be called as referential integrity. If there is any dependencies on the parent table that is dept then we are not suppose to delete the parent record from the parent table. If there is no parent values in the parent table. We can’t enter the values within the child table that is employee table.
Super keys and Candidate keys
Definition
A super key is a combination of attributes that can be uniquely used to identify a database record. A table might have many super keys. Candidate keys are a special subset of super keys that do not have any extraneous information in them.
Examples
Imagine a table with the fields <Name>, <Age>, <SSN> and <Phone Extension>. This table has many possible super keys. Three of these are <SSN>, <Phone Extension, Name> and <SSN, Name>. Of those listed, only <SSN> is a candidate key, as the others contain information not necessary to uniquely identify records.
E.g
create table child1(no number(10),sal number(10),constraint bhargav foreign key(sal) references Child(no));
Note
- We should give the foreign key relationship to another table which has already consist primary key.
- We can give the foreign key relationship to parent table’s primary key initiated column only.
ON DELETE CASCADE
This option is used to delete the parent “record” from the parent table even though there are some dependencies.
Note
It is possible to give only at the time of creating foreign key constraint.
E.g
create table baju1 (no number(10), foreign key(no) references baju(no) on delete cascade);
6. Default
This constraint is used to give default value into a particular column in the table.
E.g
create table baju2 (no number(10) default 111);
Example for applying the constraint after creating the table
alter table baju1 modify(no number(10) unique);
Note
Here we can modify previous table constraints only.
Syntax to see the list of the Constraints
select * from user_cosntraints;
Create User Defined Constraint names
create table iron(no number(10) constraint rohit primary key,name varchar(10)); select Constraint_name,Constraint_type,status from user_constraints where table_name='IRON'; CONSTRAINT_NAME C STATUS ---------------- --- -------- ROHIT P ENABLED
Note
It shows C as constraint for check and not null but first priority for not null.
Syntax to drop/enable/disable the Constraint
Syntax
ALTER TABLE<Table name> DISABLE/ENABLE/DROP CONSTRAINT CONSTRAINT_NAME
E.g
alter table iron disable constraint rohit; alter table iron enable constraint rohit; alter table iron drop constraint rohit;
Composite Primary key
Composite primary key means share primary key properties to more than one column at a time in the same table.
E.g
create table vinod(no number(10),name varchar(10),sal number(10),constraint pse primary key(no,name,sal));
Note
Here we can insert same values into columns, different values but do not give null value. But we can give duplicate values.
Copy a table from one table to another table
Syntax
CREATE TABLE<Target table name> AS SELECT * FROM <Source Table name>
E.g
create table ashok as select * from vinod;
Syntax to copy a table to another table with specific columns only
CREATE TABLE TABLE NAME<Target table name> as select col1,col2,-------,coln from <source table name>
E.g
create table ashok1 as select no,name from vinod;
Copying a table from one table to another table without data
CREATE TABLE<Target table name> as select * from <Source table name> where <false condition>
E.g
create table ranga3 as select * from vinod where 1=2; Name Null? Type --------- -------- ----- NO NUMBER(10) NAME VARCHAR2(10) SAL NUMBER(10)
Insert the data from one table to another table
Syntax
insert into elora1 select * from elora;
Note
We can insert values from one table to another table the table contains same structure and same data types. Otherwise we can’t insert.