CONSTRAINTS.SQL

-->>CONSTRAINTS


------>>>>>>>>>>>.<<<<<<<<<<<>>>>>>>>>>>>>>.<<<<<<<<<<<<------------ -->NOT NULL CONSTRAINTS
CREATE TABLE branch_mstr
(
branch_no VARCHAR2(10) CONSTRAINT branch_no_NN NOT NULL,
NAME VARCHAR2(20) CONSTRAINT name_NN NOT NULL
)

-->UNIQUE CONSTRAINTS
CREATE TABLE branch_mstr
(
branch_no VARCHAR2(10) CONSTRAINT branch_no_unq unique,
NAME VARCHAR2(20) CONSTRAINT name_NN NOT NULL
)
CREATE TABLE UNIQE
(
branch_no VARCHAR2(10) CONSTRAINT branch_no_unq unique,
NAME VARCHAR2(20) CONSTRAINT name_NN NOT NULL
)

/*****************************************************************************/

Constraints Assignments

1.Prepare select statement to give list of columns,Constraint names which have Constraint on EMP table .

select constraint_name, column_name from user_cons_columns where table_name='V_EMPLOYEE';

2.Create DEPT table

create table v_dept(deptno number,dname varchar2(15),Location varchar2(20));

3.Create primary keys columns on EMP,DEPT tables created.

alter table v_employee add constraint vemployee_empno_pk PRIMARY KEY(empno);

alter table v_dept add constraint vdept_deptno_pk PRIMARY KEY(deptno);

4.Add Foriegn key column in EMP table.

alter table v_employee

add constraint vemployee_deptno_fk

foreign key(deptno)

references v_dept(deptno);

5.Disable primary key Constraint on Dept table

alter table v_dept disable constraint vdept_deptno_pk cascade;

6.create table person with person_id and status columns.Create constaint for Status Y and N on person table

create table v_person(person_id number,status varchar2(5) check(status='Y' or status= 'N'));

7.Create table person with person_id and status columns.Create constaint on person_id column with name as "primary_cons_person_id" and rename to some other name

altertable v_person rename constraint primary_cons_person_id TO Primary_cons_Person;

ALTERING THE TABLE

Pracice Exercise #1

Based on the departments table below, rename the departments table to depts.

CREATE TABLE departments

( department_id number(10) not null,

department_name varchar2(50) not null,

CONSTRAINT departments_pk PRIMARY KEY (department_id)

);

rename Departments to Depart;

Practice Exercise #2:

Based on the employees table below, add a column called salary that is a number(6) datatype.

CREATE TABLE employees

( employee_number number(10) not null,

employee_name varchar2(50) not null,

department_id number(10),

CONSTRAINT employees_pk PRIMARY KEY (employee_number)

);

alter table employees add (salary number);

Practice Exercise #3:

Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.

CREATE TABLE customers

( customer_id number(10) not null,

customer_name varchar2(50) not null,

address varchar2(50),

city varchar2(50),

state varchar2(25),

zip_code varchar2(10),

CONSTRAINT customers_pk PRIMARY KEY (customer_id)

);

alter table customers add(contact_name varchar2(50),last_contacted date);

Practice Exercise #4:

Based on the employees table below, change the employee_name column to a varchar2(75) datatype.

CREATE TABLE employees

( employee_number number(10) not null,

employee_name varchar2(50) not null,

department_id number(10),

CONSTRAINT employees_pk PRIMARY KEY (employee_number)

);

alter table employees modify employee_name varchar2(75);

Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.

CREATE TABLE customers

( customer_id number(10) not null,

customer_name varchar2(50),

address varchar2(50),

city varchar2(50),

state varchar2(25),

zip_code varchar2(10),

CONSTRAINT customers_pk PRIMARY KEY (customer_id)

);

alter table employees add constraint ck not null customer_name;

Practice Exercise #6:

Based on the employees table below, drop the salary column.

CREATE TABLE employees

( employee_number number(10) not null,

employee_name varchar2(50) not null,

department_id number(10),

salary number(6),

CONSTRAINT employees_pk PRIMARY KEY (employee_number)

);

aler table employees drop column salary;

Based on the departments table below, rename the department_name column to dept_name.

CREATE TABLE departments

( department_id number(10) not null,

department_name varchar2(50) not null,

CONSTRAINT departments_pk PRIMARY KEY (department_id)

);

ater table departments rename column department_name to dept_name;

TRUNCATE

Practice Exercise #8:

Create a sample table and insert data try to truncate the date from the table

truncate table emp_v;