Oracle definition Constraints

FOREIGN KEY constraints guarantee referential integrity. A FOREIGN KEY constraint restricts the range of values for a column. An example is defining a state abbreviation in a finite value set, which is another control structure--a parent table

Below we create a reference table that provides a complete list of state abbreviations and then uses referential integrity to ensure that students have the correct state abbreviations. The first table is the State reference table, and it is the primary key

the four types of errors above have one error code : ORA-02291

Referential integrity is a key part of database design . A table that is neither the parent table nor the child table of the other table is very small.

Second, cascade Delete

Foreign key syntax has an option to specify cascading deletion characteristics. This feature is used only as a DELETE statement for the parent table.

With this option, a delete operation of the parent table will automatically delete all related child table records.

Use the DELETE Cascade option that creates the foreign key constraint, and then follow a DELETE statement to delete the California records in the State_lookup table and all the students with California licenses in the students table.

ALTER TABLE Students
ADD CONSTRAINT Fk_students_state
FOREIGN KEY (state) REFERENCES State_lookup (state)
On DELETE CASCADE;
To execute a DELETE statement:
DELETE from state_lookup WHERE state = ' CA ';

Then querying the data in the students table, there is no record of the field state value as CA.

If there is a foreign key association between the tables, but the CASCADE delete option is not used, the delete operation will fail.

The following issues need to be considered when defining a cascading delete:

1. is cascade deletion suitable for this application? Deleting from a parent reference table should not delete a customer account

2. What is the defined chain? View the table's association with other tables, consider the potential impact and the magnitude of the deletion and how it will affect

If you cannot cascade Delete, you can set the child table foreign key field value to NULL, using the ON DELETE SET NULL statement (Foreign key field cannot set a NOT NULL constraint).

ALTER TABLE Students
ADD CONSTRAINT Fk_students_state
FOREIGN KEY (state) REFERENCES State_lookup (state)
On DELETE SET NULL;

Third, reference field syntax structure

Creating a FOREIGN KEY constraint is that the foreign key field refers to the parent table's primary KEY or UNIQUE constraint field. In this case, you may not specify a foreign key reference field name, as follows: ALTER TABLE students ADD CONSTRAINT fk_students_state FOREIGN KEY (state) REFERENCES State_lookup When no reference field is specified, the default reference field is the primary key of the parent table.

If the foreign key field references a unique rather than a primary key field, you must specify the field name in the Add constraint statement.

Iv. referential integrity between different user patterns and database instances

FOREIGN KEY constraints can be applied between different user patterns and different database instances, but from a database management standpoint, this is not desirable. It is more appropriate for an application to be placed in a user mode. No more details here.

2), Parent-delete

Sql> DELETE from State_lookup
2 WHERE state = ' CA ';

DELETE from State_lookup
*
ERROR at line 1:
Ora-02292:integrity constraint (SCOTT. Fk_students_state)
violated per child record found

3), Child-insert

Sql> INSERT into STUDENTS
2 VALUES (' A000 ',
3 ' Joseph ', ' history ', ' degree ', ' XX ', ' mv-232-00 ');

INSERT into STUDENTS
*
ERROR at line 1:
Ora-02291:integrity constraint (SCOTT. Fk_students_state)
Violated-parent Key not found

4), Child-update

Sql> UPDATE Students
2 SET state = ' XX '
3 WHERE student_id = ' A103 ';

UPDATE Students
*
ERROR at line 1:
Ora-02291:integrity constraint (SCOTT. Fk_students_state)
Violated-parent Key not found

Referential integrity rules are enforced during the parent table update deletion and during the child table insert Update. The SQL statement affected by referential integrity is: The Parent-update parent Table Update operation does not update the state value in the State_lookup table to a value that students table is still in use and does not have in the State_lookup table.

Parent-delete The parent table delete operation does not delete the state value in the State_lookup table causes the students table to still be in use and the State_lookup table does not have this value.

Child-insert the child table insert operation cannot insert a value of state that is not in a state_llokup table child-update the child table update operation cannot update the value of state to the value of State not in the State_lookup table

The following example shows four types of errors:

The test table structure and test data are as follows:

State_lookup
--------------------------------
State State Description
CA California
NY New York
NC North Carolina
---------------------------------
STUDENTS
--------------------------------------------------------------------------------
Student ID Student Name College Major Status State License NO
A101 John biology degree NULL NULL
A102 Mary math/science degree null null
A103 Kathryn History degree CA mv-232-13
A104 Steven Biology degree NY mv-232-14
A105 William 中文版 degree NC mv-232-15
--------------------------------------------------------------------------------

1), Parent-update

Sql> UPDATE State_lookup
2 SET state = ' XX '
3 WHERE state = ' CA ';

UPDATE State_lookup
*
ERROR at line 1:
Ora-02292:integrity constraint (SCOTT. Fk_students_state)
violated per child record found

CREATE TABLE State_lookup
(State VARCHAR2 (2),
State_desc VARCHAR2 (()) tablespace student_data;

ALTER TABLE State_lookup
ADD CONSTRAINT pk_state_lookup PRIMARY KEY (state)
USING INDEX tablespace Student_index;

Then insert a few lines of records:

INSERT into State_lookup VALUES (' CA ', ' California ');
INSERT into State_lookup VALUES (' NY ', ' New York ');
INSERT into State_lookup VALUES (' NC ', ' North Carolina ');

We guarantee referential integrity by implementing parent-child relationships, as shown here

---------------foreign key fields exist in the students table
|  State_lookup | Is the State field
---------------A foreign key must refer to the primary key or unique field
| In this example, we are referring to the State field
| It is a primary key field (see DDL)
/|/
---------------
| Students |
---------------

Shows a one-to-many relationship between the State_lookup table and the students table, and the State_lookup table defines a common set of state abbreviations-one in each state of the table. Therefore, the primary key of the State_lookup table is the State field.

A state name in the State_lookup table can appear multiple times in the students table. There are many students from the same state, once, the referential integrity between tables State_lookup and students implements a one-to-many relationship.

The foreign key also guarantees the integrity of the state field in the students table. Each student always has a state abbreviation for a member of the State_lookup table.

Foreign KEY constraints are created in the child table. The following creates a foreign key constraint on the students table. The state field references the primary key of the State_lookup table.

1. Create a table

CREATE TABLE Students
(student_id VARCHAR2 (Ten) not NULL,
Student_name VARCHAR2 (+) not NULL,
College_major VARCHAR2 () not NULL,
Status VARCHAR2 () not NULL,
State VARCHAR2 (2),
License_no VARCHAR2 (()) tablespace student_data;

2. Create a PRIMARY key

ALTER TABLE Students
ADD CONSTRAINT pk_students PRIMARY KEY (student_id)
USING INDEX tablespace Student_index;

3. Create a UNIQUE constraint

ALTER TABLE Students
ADD CONSTRAINT Uk_students_license
UNIQUE (state, License_no)
USING INDEX tablespace Student_index;

4. Create a CHECK Constraint

ALTER TABLE Students
ADD CONSTRAINT Ck_students_st_lic
CHECK ((State was null and LICENSE_NO is null) OR
(State was not null and LICENSE_NO are NOT null));

5. Create a FOREIGN KEY constraint

ALTER TABLE Students
ADD CONSTRAINT Fk_students_state
FOREIGN KEY (state) REFERENCES state_lookup (state);

First, four types of errors

Oracle definition constraints