Oracle Data integrity constraints: Statement for creating and deleting primary keys, foreign keys, and various constraints

Oracle constraints on database integrity:

Three methods to maintain data integrity: ORACLE integrity constraints, database triggers, and application code.

ORACLE integrity constraints should be used whenever possible, with high reliability and efficiency, easy to modify, flexible to use, and recorded in the data dictionary.

Five ORACLE constraints:

Non-null not null. The defined Column cannot be empty. Can be defined only at the column level

Unique, unique. The column or column value defined by each row in the table cannot be the same

The primary key cannot contain null values. The primary key uniquely identifies each row in the table. One or more columns are combined as the primary key.

Foreign key indicates the combination of one or more columns as foreign keys to maintain the referential integrity between the chilD table and the primary table parent table.

Condition constraints check. Each row in the table must meet the constraints. Constraint plus table, which can be defined during table Creation

1. query Constraints

Query whether there are constraints in the table and display the constraint name: displays the constraints corresponding to the table column

BYS @ bys1> select a. constraint_name, a. column_name from user_cons_columns a, user_constraints B where a. table_name = upper ('& ');
Enter value for aa: emp
Old 1: select a. constraint_name, a. column_name from user_cons_columns a, user_constraints B where a. table_name = upper ('& ')
New 1: select a. constraint_name, a. column_name from user_cons_columns a, user_constraints B where a. table_name = upper ('emp ')

CONSTRAINT_NAME COLUMN_NAME
---------------------------------------------
PK_EMPNO EMPNO
Check whether primary key constraints exist in the table.

BYS @ bys1> select. constraint_name,. column_name from user_cons_columns a, user_constraints B where. constraint_name = B. constraint_name and B. constraint_type = 'p' and. table_name = upper ('& table_name ');
Enter value for table_name: emp
Old 1: select. constraint_name,. column_name from user_cons_columns a, user_constraints B where. constraint_name = B. constraint_name and B. constraint_type = 'p' and. table_name = upper ('& table_name ')
New 1: select. constraint_name,. column_name from user_cons_columns a, user_constraints B where. constraint_name = B. constraint_name and B. constraint_type = 'p' and. table_name = upper ('emp ')

CONSTRAINT_NAME COLUMN_NAME
---------------------------------------------
PK_EMPNO EMPNO

Query all constraints of the current user

BYS @ bys1> col owner for a10
BYS @ bys1> col table_name for a10
BYS @ bys1> select * from user_cons_columns;
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
---------------------------------------------------------------------------
BYS PK_OBJ_ID TEST2 OBJECT_ID 1
BYS SYS_C0011203 TEST1 OBJECT_ID 1
BYS PK_EMPNO emp empno 1

 

Query the status information of related constraints of the current user. You can query dba_constraints or USER_constraints.

BYS @ bys1> select constraint_name, table_name, constraint_type, status, deferrable, deferred, validated from dba_constraints where owner = 'bys ';
CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED
-------------------------------------------------------------------------------------
SYS_C0011203 TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_EMPNO EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_OBJ_ID TEST2 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

SCOTT @ bys1> select constraint_name, table_name, constraint_type, status, deferrable, deferred, validated from user_constraints;
CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED VALIDATED
-------------------------------------------------------------------------------------
FK_DEPTNO EMP R ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_DEPT DEPT P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_EMP EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
SYS_C0011265 TEST P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
PK_A TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

2. add, delete, and modify constraints:

Add or delete primary keys and Foreign keys

Alter table dept add constraint pk_dept primary key (deptno );

Alter table dept2 add primary key (dname); If the constraint name is not specified, the constraint name is automatically bound by the system command.
Alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno) on delete cascade;
Alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno );

Note: The functions of on delete cascade and on delete set null are used to deal with cascading deletion problems,

If the data you want to delete is referenced by other data (primary and Foreign keys), you should decide how you want oracle to process the data that will be deleted.

You can use either of the following methods:
Delete prohibited. This is also the default oracle version.
Null is assigned to the corresponding columns of data with reference to the current value, and the on delete set null keyword is used. That is, the values in the columns of the current table are deleted. The corresponding values of the corresponding columns of the table with foreign key references in this column are modified to NULL.
Delete the data with reference to the current value and use the on delete cascade keyword to delete the values in the columns of the table, the corresponding columns of the table with foreign key reference for this column will be deleted

Add CHECK constraints:

Alter table dept add constraint valid_deptno check (deptno <5000); the value of the DEPTNO column must be less than 5000

SQL> alter table customer add constraint abc check (address in ('haidian ', 'chaoyang', 'dongcheng ', 'xicheng', 'tongzhou ', 'chongwen ', 'changping '). The customer's address can only be 'haidian', 'chaoyang ', 'dongcheng', 'xicheng ', 'tongzhou', 'chongwen', or 'changping ';

Add a unique constraint

Alter table customer add constraint aaa unique (cardId );

ADD a non-empty constraint -- note that when adding a non-empty constraint, the keyword modify is used. The other four constraints are "ADD ".

SQL> alter table dept modify dname not null;

Delete

Delete A non-empty constraint: because a non-empty constraint cannot specify a constraint name, you should first query the constraint information corresponding to the table and column, locate the constraint name, and then delete it. As follows:

BYS @ bys1> alter table dept drop constraint SYS_C0011725;

Modify constraints -- deleted and rebuilt

Alter table dept2 modify constraint pk_d2 initially immediate;

Delete the constraint of the specified name-it can be the name of the primary key, external creation, or other constraint

Alter table emp drop constraint PK_EMPNO;

Alter table scott. event drop constraint evtid_pk; Delete the constraints of other users -- DBA permission required

You do not need to specify the constraint name to directly Delete the primary key in the table.

Altertable emp drop primary key cascade; When deleting the primary key constraint, if the on delete cascade parameter is not specified when the foreign key on some primary keys is created, an error is returned when the primary key is deleted directly. The cascade parameter must be added.

Heavy command Constraints

Alter table scott. emp rename constraint pk_emp to emp_empno_pk;

For more details, please continue to read the highlights on the next page:

Oracle integrity constraints

Oracle constraints and Indexes

From Oracle constraints to Indexes

Common Oracle Data Types and integrity constraints

ORA-02291: violation of complete constraints ...... -The parent keyword is not found.

1 2 Next Page