PART 3

1. PART 3 SQL

2. Commit & rollback • Insertions, deletions and updations into or from a database can be reversed or rolled back. Rollback; • Inserts, deletes and updates cannot be made final until committed. That is they will not affect the table Commit; • All DDL statements are equivalent to giving a commit.

3. Auto Rollback: In case of system failure Oracle automatically rolls back any uncommitted work. • Auto Commit: automatically commits the work Set autocommit on;

4. INTEGRITY Constraints • Data integrity indicates the consistency and correctness of data in a database. • Integrity Constraints are the rules enforced on a table at the time of its creation • It prevents the user from entering invalid data into tables.

5. Integrity Constraints: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • Default

6. NOT NULL • Column definition as not null makes it mandatory for the user to enter a value for that column. • Create table emp(empnovarchar(10) NOT NULL); • NOT NULL constraint can only be applied at the column level.

7. Unique Key Constraint • Ensures that the value entered for a column is unique i.e. value is not duplicated in a column. • Unique key can accept NULL value. • NOT NULL and Unique constraints can be applied to a single column. Eg: CREATE TABLE student ( roll_number number (5) NOT NULL UNIQUE, Name Varchar(20), course Varchar(10));

8. Unique key constraint can be applied to multiple columns Eg: CREATE TABLE student (roll_number number (5) NOT NULL UNIQUE, Name Varchar(20), address Varchar(50), dateofbirth date, course Varchar(10), CONSTRAINTu_keyUNIQUE(address,dateofbirth)); • Address and date of birth columns may contain duplicate values individually but the combination of address and date of birth must not contain duplicate values. • U_key is the name of the constraint.

9. Oracle allows constraints to be defined at • Column level • Table level Table level constraints: • If data constraints are defined after defining all the table columns when creating or altering a table structure ,it is a table level constraint. • Appears at the end of a CREATE TABLE statement. • Method of defining a table level constraint is called out-line-specification

10. Column level Constraint: • If data constraints are defined along with the column definition when creating or altering a table structure, they are columnlevel constraints. • Appears at the end of a column definition. • Is applicable on the column on which it is specified. • Method of defining a column level constraint is called in-line-specification

11. Primary Key Constraint • A combination of UNIQUE and NOT NULL constraints. • Ensures that all the values in the column are unique • Donot apply UNIQUE AND PRIMARY key constraint on the same column. • Primary key must never contain a NULL value unlike UNIQUE key constraint

12. Defining PRIMARY KEY constraint at column level CREATE TABLE student (enrollment_novarchar(20) PRIMARY KEY, roll_number number (5) NOT NULL UNIQUE, Name Varchar(20), address Varchar(50), dateofbirthdate,courseVarchar(10), CONSTRAINTu_keyUNIQUE(address,dateofbirth));

13. Defining PRIMARY KEY constraint at table level CREATE TABLE student (enrollment_novarchar(20) , roll_number number (5) NOT NULL UNIQUE, Name Varchar(20), address Varchar(50), dateofbirth date, course Varchar(10), CONSTRAINTP_keyPRIMARY KEY(enrollment_no, address));

14. FOREIGN KEY CONSTRAINT • Also known as referential integrity constraint. • A relationship between two tables in a database is created by using a common column in both the tables. • The parent table’s primary key appears in another table called as child table as the foreign key. • Used to maintain the referential integrity of the data. • The value entered in a foreign key must match the value of the primary key also known as the referenced key.

15. Constraint at column level: Create table department(depno number(5) primary key, deptnamevarchar(20)); CREATE TABLE employee( empid number(5) CONSTRAINTprimaryempPRIMARY KEY, departid number(5) REFERENCES department(depno), name varchar(15), address varchar(50));

16. Constraint at table level: CREATE TABLE employee( empid number(5) CONSTRAINTprimaryempPRIMARY KEY, deptid number(5) ,deptnamevarchar(10), name varchar(15), address varchar(50), CONSTRAINT deptfkeyFOREIGN KEY(deptid,deptname) REFERENCES department(depno,deptname));

17. Check Constraint • Used to restrict values entered in a column . CREATE TABLE student (enrollment_novarchar(20) PRIMARY KEY, roll_number number (5) NOT NULL UNIQUE, Name Varchar(20), address Varchar(50), dateofbirthdate,courseVarchar(10) CHECK(course in(‘BScCS’,’MSc CS’, ‘MCA’, ‘Btech’)));

18. CHECK can be applied on multiple columns of a table. CREATE TABLE student (enrollment_novarchar(20) PRIMARY KEY, roll_number number (5) NOT NULL UNIQUE, Name Varchar(20), address Varchar(50), dateofbirthdate,courseVarchar(10) CHECK(course in(‘BScCS’,’MSc CS’, ‘MCA’, ‘Btech’)) CONSTRAINT roll_check CHECK ( roll_number >=1 and roll_number<=50));

19. Default constraint Specifies a value that is to be inserted in a column • Defining default constraint at column level: CREATE TABLE student (enrollment_novarchar(20) PRIMARY KEY, roll_number number (5) NOT NULL UNIQUE, Name Varchar(20), address Varchar(50), dateofbirthdate,courseVarchar(10) DEFAULT ‘BSc CS’ ), CONSTRAINT roll_check CHECK ( roll_number >=1 and roll_number<=50));

20. Adding & dropping constraints • Constraints can be added into an existing table. • Constraint can be dropped when no longer needed.

21. Adding constraint • ALTER TABLE student ADD CONSTRAINT stud_pkeyPRIMARY KEY ( enrollment_number); • ALTER TABLE employee ADD CONSTRAINT emp_fkeyFOREIGN KEY (deptid) REFERENCES department(deptid); • ALTER TABLE student ADD CONSTRAINT student_ukUNIQUE (roll_number);

22. ALTER TABLE student ADD CONSTRAINT student_ckCHECK (course in(‘BSc CS’, MSc CS’, ‘MCA’, ‘Btech’)); • Use MODIFY for adding NOT NULL and DEFAULT constraint. ALTER TABLE student MODIFY course DEFAULT ‘BSc CS’ ; ALTER TABLE student MODIFY address NOT NULL;

23. DROPPING CONSTRAINTS • ALTER TABLE student DROP CONSTRAINT PRIMARY KEY; OR • ALTER TABLE student DROP CONSTRAINT student_Pkey; • A primary key cannot be dropped if it is being referenced by a foreign key in a child table

24. ALTER TABLE student DROP UNIQUE (address) ; • ALTER TABLE student DROP CONSTRAINT (student_Ukey); • Constraint names have to be specified while dropping other constraints.