Example Of Constraints in Sql

Primary Key:- YOU CAN NOW CREATE A PRIMARY KEY IN oracle with the CREATE TABLE STATEMENT.
SYNTAX
THE SYNTAX TO CREATE A PRIAMARY KEY USING THE CREATE TABLE STATEMENT IN ORACLE/PLSQL IS:

create table empl(employee_id number primary key,
last_name varchar2(50),
city varchar2(50))
/

Syntax

Alter table table_name
Add column column_defination
Add constraint clause
drop[column] column-name [cascade|Restrict]
drop{primary key}foreign key constraint_name | unique
constraint_name|check constraint-name|constraint constraint-name}
alter [column] column_alteration|locksize {Row |Table}

alter table emp1 add phone number;


In oracle Sql-PLSQL a foreign key is column that appears in one table and must appear in another table.

Create table table_name
(Column_name1 datatype Null/Not null,
Column_name2 datatype Null/Not null,
Column_name3 datatype Null/Not null,
constraint constraint_name
foreign key(column_name1,column_name2....column_namen)
On delete cascade;

Syntax to create a foreign key with on Delete Cascade in alter table statement is:

Alter Table Table_name
Add Constraint constraint_name
Foreign key (Column_name1,Column_name2,....Column_nameN)
On Delete Cascade;

Now we will take one example:-



create table dept(department_id number,
department_name varchar2(50),
location_id number,
city varchar2(50));

alter table dept add constraint pk10 primary key(department_id);

create table emp(employee_id number,last_name varchar2(50),
salary number(10,2),
department_id number )
/

alter table emp add constraint fk14 foreign key(department_id)
references dept(department_id) on delete cascade;

/

How to check user constraints from employees table

select constraint_name,constraint_type,index_name
from user_constraints
where table_name='EMPLOYEES'
/