Oracle ---Constraints

上一篇 / 下一篇  2010-03-30 21:34:32 / 个人分类：Oracle

查看( 565 ) / 评论( 0 ) / 评分( 0 / 0 )
There are some types of constraints:
NOT NULL
UNIQUE key
PRIMARY KEY
FOREIGN KEY
CHECK
Query the USER_CONSTRAINTS table to view all constraint definitions and names.
 
1. Oracle Server uses constraints to prevent invalid data entry into tables.
You can use constraints to do the following:
Enforce rules at the table level whenever a row is inserted, updated, or deleted from that table. The constraint must be satisfied for the operation to succeed.
Prevent the deletion of a table if there are dependencies from other tables.
Provide rules for Oracle tools, such as Developer/2000.
 
2.Constraint Guidelines
All constraints are stored in the data dictionary. Constraints are easy to reference if you give them a meaningful name. Constraint names must follow the standard object naming rules. If you do not name your constraint, Oracle generates a name with the format SYS_Cn, where n is an integer to create a unique constraint name.
Constraints can be defined at the time of table creation or after the table has been created.
You can view the constraints defined for a specific table by looking at the USER_CONSTRAINTS data dictionary table.
 
3. Define constraint
Defined at the column level or table level
1) not null
SQL> CREATE TABLE emp(
  2      empno    NUMBER(4),
  3     ename   VARCHAR2(10) NOT NULL,
  4     job     VARCHAR2(9),
  5     mgr     NUMBER(4),
  6     hiredate        DATE,
  7     sal     NUMBER(7,2),
  8      comm    NUMBER(7,2),
  9     deptno  NUMBER(7,2) NOT NULL);
 
SQL> CREATE TABLE   dept(
  2      deptno     NUMBER(2),
  3     dname     VARCHAR2(14),
  4     loc       VARCHAR2(13),
  5     CONSTRAINT dept_dname_uk UNIQUE(dname));
 
2)Unique key, primary key
SQL> CREATE TABLE   dept(
  2      deptno     NUMBER(2),
  3     dname     VARCHAR2(14),
  4     loc       VARCHAR2(13),
  5     CONSTRAINT dept_dname_uk UNIQUE (dname),
  6     CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));
 
3)The FOREIGN KEY Constraint
The FOREIGN KEY, or referential integrity constraint, designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table. In the example on the slide, DEPTNO has been defined as the foreign key in the EMP table (dependent or child table); it references the DEPTNO column of the DEPT table (referenced or parent table).
A foreign key value must match an existing value in the parent table or be NULL.
Foreign keys are based on data values and are purely logical, not physical, pointers.
SQL> CREATE TABLE emp(
  2      empno    NUMBER(4),
  3     ename   VARCHAR2(10) NOT NULL,
  4     job     VARCHAR2(9),
  5     mgr     NUMBER(4),
  6     hiredate        DATE,
  7     sal     NUMBER(7,2),
  8      comm    NUMBER(7,2),
  9     deptno  NUMBER(7,2) NOT NULL,
10     CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11                     REFERENCES dept (deptno));
 
The foreign key is defined in the child table, and the table containing the referenced column is the parent table. The foreign key is defined using a combination of the following keywords:
FOREIGN KEY is used to define the column in the child table at the table constraint level.
REFERENCES identifies the table and column in the parent table.
ON DELETE CASCADE indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted.
Without the ON DELETE CASCADE option, the row in the parent table cannot be deleted if it is referenced in the child table.
 
 
4)The CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as query conditions, with the following exceptions:
References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows
..., deptno     NUMBER(2),
      CONSTRAINT emp_deptno_ck 
            CHECK (DEPTNO BETWEEN 10 AND 99),...
 
5.Adding a Constraint
You can add a constraint for existing tables by using the ALTER TABLE statement with the ADD  clause.
In the syntax:
ALTER TABLE      table
  ADD [CONSTRAINT constraint] type (column);
Guidelines
You can add, drop, enable, or disable a constraint, but you cannot modify its structure.
You can add a NOT NULL constraint to an existing column by using the MODIFY clause of the ALTER TABLE statement.
 
 
6.Dropping a Constraint
Remove the manager constraint from the EMP table.
SQL> ALTER TABLE          emp
  2  DROP CONSTRAINT  emp_mgr_fk;
Table altered.
 
7.Disabling a Constraint
You can disable a constraint without dropping it or recreating it by using the ALTER TABLE statement with the DISABLE clause.
SQL> ALTER TABLE                emp
  2  DISABLE CONSTRAINT emp_empno_pk CASCADE;
Apply the CASCADE option to disable dependent integrity constraints.
 
8.Guidelines
If you enable a constraint, that constraint applies to all the data in the table. All the data in the table must fit the constraint.
If you enable a UNIQUE key or PRIMARY KEY constraint, a UNIQUE or PRIMARY KEY index is automatically created.
You can use the ENABLE clause in both the CREATE TABLE statement and the ALTER TABLE statement.
SQL> ALTER TABLE                emp
  2  ENABLE CONSTRAINT  emp_empno_pk;
Table altered.
 
9.Viewing Constraints
Viewing Constraints
After creating a table, you can confirm its existence by issuing a DESCRIBE command. The only constraint that you can verify is the NOT NULL constraint. To view all constraints on your table, query the USER_CONSTRAINTS table.
SQL>  SELECT    constraint_name, constraint_type,
  2             search_condition
  3   FROM      user_constraints
  4   WHERE     table_name = 'EMP';
You can view the names of the columns involved in constraints by querying the USER_CONS_COLUMNS data dictionary view. This view is especially useful for constraints that use the system-assigned name.
SQL> SELECT     constraint_name, column_name
  2  FROM       user_cons_columns
  3  WHERE      table_name = 'EMP';

收藏 举报

TAG:

 

评分：0

我来说两句

Open Toolbar