Introduction to Oracle Constraints

1. Classification of constraints.

Constraints are divided into five categories: 1. not null, 2. primary key, 3. check, 4. unique, 5. foreign key.

1.1 not null Constraint

By default, the values of all columns can contain null values. When the not null constraint is defined on a column, the column must have a value above it. The not null constraint is often used together with other constraints. For example, if it is used together with the unique constraint, the data of the newly inserted column will not conflict with the existing data. When you need to create an index on a considerable number of columns, we recommend that you add the not null constraint on the relevant columns because the index does not store null records.

1.2 primary key constraints

A primary key constraint is a combination of not null constraints and unique constraints to ensure that row records are unique and non-repetitive. Each table can have only one primary key constraint. During table design, we usually have a primary key constraint on each table. When creating a primary key, you will create an index for the corresponding constraint name. When selecting a primary key constraint column, you can refer to the following instructions:

1. Select the sequence column as the primary key.

2. Select a column with a unique value and no null value.

3. The primary key is generally not modified, and is only used to identify the uniqueness of the row. It is not used for other purposes.

4. Try to select a short value or a number value for the primary key column.

1.3 unique constraints

The unique constraint ensures that the record of the value does not have the same value, but the noll value is not authorized. when creating the unique constraint, the index of the constraint name will be created by the user.

1.4 check Constraints

Check constraints are used to check whether the value meets the specified conditions during insertion. For example, the value must be greater than 10 and less than 100.

1.5 foreign key constraint

When the values of the columns in Table A must be the values of the columns in Table B, A foreign key constraint can be defined. The values related to the parent table have primary keys or uniqueness constraints. However, many companies require that foreign keys not be used, so developers can use their own programs to determine.

2. Definition of constraints

Constraints can be specified when a table is created, or you can use the alter command after the table is created. The syntax for creating each constraint is as follows.

1. 2.1 not null Constraint
2.  
3. Create table test_cons (id number constraint cons_test_cons_id_nonull not null );
4.  
5. Create table test_cons_1 (id number not null );
6.  
7. Alter table test_cons_2 modify id not null;
8.  
9. 2.2 primary key
10.  
11. Create table test_cons_1 (id number primary key );
12.  
13. Create table test_cons_2 (id number constraint cons_2 primary key );
14.  
15. Create table test_cons_3 (id number, constraint cons_3 primary key (id ));
16.  
17. Create table test_cons_4 (id number );
18.  
19. Alter table test_cons_4 add constraint cons_4 primary key (id );
20.  
21. 2.3 unique
22.  
23. Create table test_cons_1 (id number unique );
24.  
25. Create table test_cons_2 (id number constraint cons_2 unique );
26.  
27. Create table test_cons_3 (id number, constraint cons_3 unique (id ));
28.  
29. Create table test_cons_4 (id number );
30.  
31. Alter table test_cons_4 add constraint cons_4 unique (id );
32.  
33. 2.4 check
34.  
35. Create table test_cons_1 (id number check (id> 10 and id <100 ));
36.  
37. Create table test_cons_2 (id number constraint cons_2 check (id> 10 and id <100 ));
38.  
39. Create table test_cons_3 (id number, constraint cons_3 check (id> 10 and id <100 ));
40.  
41. Create table test_cons_4 (id number );
42.  
43. Alter table test_cons_4 add constraint cons_4 check (id> 10 and id <100 );
44.  
45. 2.5 Foreign key constraints
46.  
47. Create table test_cons_1 (id number, constraint cons_1 foreign key (id) references test_cons (id ));
48.  
49. Alter table test_cons_4 add constraint cons_4 foreign key (id) references test_cons (id );

1 2 Next Page