Oracle Management Constraints (II) Oracle Maintenance constraints

1. Define constraints when you build a table, either at the column level or at the table level. For a NOT NULL constraint, it can only be defined at the column level and cannot be defined at the table level.

(1) Column-level definition

Grammar:

column [CONSTRAINT constraint_name] Constraint_type,

02:01:01 sql> CREATE TABLE T1 (

02:01:19 2 Deptno number (2) Constraint Pk_deptid primary key,

02:01:25 3 name VARCHAR2 (20),

02:01:30 4 loc varchar2 (20));

Table created.

02:12:07 sql> desc T1;

Name Null? Type

--------- -------- -----------------

DEPTNO not NULL number (2)

NAME VARCHAR2 (20)

LOC VARCHAR2 (20)

02:12:15 sql> INSERT INTO T1 values (' Cisco ', ' BeiJing ');

1 row created.

02:12:59 sql> INSERT INTO T1 values (' Cisco ', ' BeiJing ');

INSERT into T1 values (' Cisco ', ' BeiJing ')

*

ERROR at line 1:

Ora-00001:unique constraint, SCOTT. Pk_deptid) violated

(2) Table-level definition

Grammar:

Column, ...,

[CONSTRAINT constraint_name] constraint_type (column, ...,),

CREATE TABLE T2 (

2 empno Number (4), name VARCHAR2, job varchar2 (10),

3 manager_id Number (4), hire_date date,sal number (7,2),

4 Comm Number (7,2), Deptno number (2),

5 constraint pk_emp_id primary KEY (EMPNO)

6 using index tablespace users01,

7 Constraint fk_dept_id foreign key (DEPTNO)

8 References T1 (DEPTNO));

Table created.

03:43:32 sql> SELECT * from T1;

DEPTNO NAME LOC

---------- -------------------- --------------------

Ten Cisco BeiJing

03:43:46 sql> INSERT INTO T2 values (7788, ' Tom ', ' Sales ', ' 7936 ', sysdate,3000,0,20);

INSERT into T2 values (7788, ' Tom ', ' Sales ', ' 7936 ', sysdate,3000,0,20)

*

ERROR at line 1:

Ora-02291:integrity constraint, SCOTT. fk_dept_id) violated-parent key not found

In the T1 deptno is not 20, so inserting 20 of the record error, referential constraint.

03:44:59 sql> INSERT INTO T2 values (7788, ' Tom ', ' Sales ', ' 7936 ', sysdate,3000,0,10);

1 row created.

03:45:04 sql> select * from T2;

EMPNO NAME JOB manager_id hire_date SAL COMM DEPTNO

---------- --------------- ---------- ---------- ------------------- ---------- ---------- ----------

7788 Tom Sales 7936 2011-02-25 03:45:04 3000 0 10

2. Increase the constraint after building the table

(1) Not Null

The NOT NULL constraint can only be modified with modify.

04:19:22 sql> ALTER TABLE test modify ename NOT null;

Table altered.

(2) Unique

04:27:41 sql> ALTER TABLE test

04:30:25 2 Add constraint uni_ename unique (ename);

Table altered.

04:32:06 sql> ALTER TABLE test

04:32:07 2 Add constraint uni_ename unique (ename)

04:32:10 3 using index (CREATE index ind_name on test (ename));

Add constraint uni_ename unique (ename)

*

ERROR at line 2:

Ora-02261:such the unique or primary key already exists in the table

04:39:10 sql> ALTER TABLE T1

04:39:17 2 Add constraint u_name unique (name)

04:39:20 3 using index (CREATE index ind_name on T1 (name));

Table altered.

(3) Check

04:42:10 sql> ALTER TABLE T1

04:42:13 2 Add constraint chk_loc check (loc in (' BeiJing ', ' Shanghai '));

Table altered.

04:42:19 sql> INSERT INTO T1 values (, ' Microsoft ', ' tianjing ');

INSERT into T1 values (' Microsoft ', ' tianjing ')

*

ERROR at line 1:

Ora-02290:check constraint, SCOTT. Chk_loc) violated

04:43:03 sql> INSERT INTO T1 values (, ' Microsoft ', ' Shanghai ');

1 row created.

04:43:18 sql> SELECT * from T1;

DEPTNO NAME LOC

---------- -------------------- --------------------

Ten Cisco BeiJing

C%OMMD Shanghai

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/