including Constraints

1 What are Constrains
  1) Constrains enforce on the table level
  2) Constrains the deletion of a table if there are dependencies
2 Constrain Guidelines
  1) Name a constraint or the oracle generate a name by the sys_cn format
  2) Create a constraint either
     --At the same time as the table is created.or
     --After the table has been created
  3)Define a constraint at the column or table level
  4)view constraint in the data dictionary
3 Crete a constraint
  create table test2
  (id int not null,-- column level
   lname varchar(20),
   fname varchar(20),
   constraint uk_test2_1 unique(lname,fname))--table level
4 The not null Constraint
  create table employees(
   employee_id number(6),
   last_name   varchar2(25) not null  --system named
   hire_date   DATE
               constraint emp_hire_date not null --User named
5Foreign key
  create table test3
  (rid int,
   name varchar(30),
   constraint fk_test3_1 foreign key(rid) reference test2(id));
  froeign key constraint keywords
    foreign key :Define the column in thee child table at the table constrain level.
    references  :Identifies the table and column in the parent table.
    on delete cascade: Delete the dependent rows in the child table when a row in the     parent table is deleted
    on delete set null:Convert the dependent foreign key values to null when a row in the
    parent table is deleted.
  
    --parent table referenced table
    --child table refernce other table
6 The check Constraint
  Define a condition that each row must be satisfy
  alter table test3
  add constrain ch_test3 check(name like 's%')
7 Dropping a Constraint
  1) Remove the manager constraint form the employee table
   alter table test3
   drop constriant test3_manager_fk
  2) Remove the primary key constraint on the departments table and drop the associated
   foreign key constraint on the employees.department_id column
   alter table departments
   drop primary key cascade
8 Disabling and enable Constraints
  1)Execute the disable clause of the alter table statment to deactive an integrity constraint
  2)Apply the cascade option to disable dependent integrity constrints
  alter table employees
  disable constraint emp_emp_id_pl cascade
  3) enabling Constraints
  .Active an integrity constraint currently disabled in the table definition by using the enable clause.
   alter table employees
   enable constraint emp_emp_id_pk;
  a unique  or a primary  index is automatically created if you enable a unique key or a primary key constraint 
 8 View Constraints
  select constraint_name,constriant_type,serch_condition
  from user_constraints
  where table_name='employees'
 9 view the columns associated with constraints
 select constraint_name,column_name
 from user_cons_columns
 where table_name='employees'

posted on 2006-10-11 14:45 康文 阅读(230) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2006年10月>
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

搜索

最新评论

阅读排行榜

评论排行榜