5.Table Level Constraints

SQL

Constants: Constraints are nothing but apply rules on columns in a table like
not to accept duplicate, null values, accept conditionally satisfied data etc.,

Types of constraints

  1. NOT NULL : This is used to no to accept null values in a column (null is not equal to space)
  2. UNIQUE : This is used to accept only unique records and not duplicate values
  3. PRIMARY KEY : This column will accept not null and unique values only,
    and helps to build relation between tables like one-to-one, one-to-many, many-to-many
  4. CHECK : This column will accept values conditionallyu satisified ex: sal>10000
  5. DEFAULT : This is to assign default values when no values inserted
  6. FOREIGN KEY : This is to refer the primary key column in a table when you want build the relation.

A constraint can be created at column or table level

Table Level

Syntax: sql> CREATE TABLE TABLE_NAME
(Col1 data type constraint_name,
col2 data type


constraint_name (col1,col2..) — Table level constraint
);

Example: Create employee table using constraints

SQL> create table employees_781
(eno number ,
ename varchar2(10) not null,
sal number check(sal>1000),
job varchar2(10),
doj date default sysdate,
primary key (eno,ename) — composite primary key
);

SQL> select * from employees_78;

no rows selected

Now Insert below data and check

   ENO ENAME             SAL JOB        DOJ

   100 hemanth         15000 analyst    10-MAR-14        (right)
   100 gates           15000 manager    08-MAY-15        (right)
   100 hemanth       25000 manager    09-may-15       (wrong, will give error because combination of eno,ename is repeated