SQLÓï¾äPART3

constraint Example:
1. grammer:
 create table [schema.]table
      (column datatype [DEFAULT expr]
     [column_constraint], ...
     [table_constraint] [,......]);
2. example of a column_level constraint:
create table employees(employee_id number(6) constraint emp_emp_id_pk primiary key, first_name varchar2(20), ...);
3. example of a table-level constraint:
create table employees(employee_id number(6), first_name varchar2(20), ... job_id varchar2(20) not null, constraint emp_emp_id_pk primary key(employee_id));
4. UNIQUE example
create table employees(employee_id number(6), last_name varchar2(20) not null,..., constraint emp_email_uk UNIQUE(email));
5. FOREIGN KEY constraint
create table employees(employee_id number(6), last_name varchar2(20),... constraint emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), constraint emp_email_uk UNIQUE(email));
** foreign key constaint:
** on delete cascade: deletes the dependent rows in the child table when a row in the parent table is deleted.
** on delete set null: converts dependent foreign key values to null.
6. CHECK constraint
* not allowed: 1) references to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns 2)calls to SYSDATE, UID, USER and USERENV functions. 3) queries that refer to other values in other rows
FINAL example on constraints:
CREATE TABLE employees
( employee_id NUMBER(6) CONSTRAINT emp_employee_id PRIMARY KEY
, first_name VARCHAR2(20)
, last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL CONSTRAINT emp_email_uk UNIQUE
, phone_number VARCHAR2(20)
, hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2) CONSTRAINT emp_salary_ck CHECK (salary>0)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6) CONSTRAINT emp_manager_fk REFERENCES employees (employe