Oracle Database Design (defining constraints on foreign key constraints)

 

The foreign key constraint ensures the integrity of the reference. The foreign key constraint limits the value range of a column. One example is to restrict the abbreviation of a state name in a finite value set. This value set is another control structure-a parent table.
Next we create a reference table that provides a complete list of abbreviated state abbreviations, and then uses the reference integrity to ensure that students have the correct state abbreviations. The first table is the State reference table, with the State as the primary key.
Create Table state_lookup
(State varchar2 (2 ),
State_desc varchar2 (30) tablespace student_data;

Alter table state_lookup
Add constraint pk_state_lookup primary key (state)
Using index tablespace student_index;
Insert several rows of records:
Insert into state_lookup values ('CA', 'california ');
Insert into state_lookup values ('ny ', 'New York ');
Insert into state_lookup values ('nc ', 'North region lina ');
We guarantee the integrity of the reference by implementing the parent-child relationship, as shown in the figure below:
--------------- The foreign key field exists in the students table
| State_lookup | it is the state field.
--------------- A foreign key must refer to the primary key or unique field
| In this example, we refer to the state field.
| It is a primary key field (see DDL)
/|/
---------------
| Students |
---------------
The relationship between the state_lookup table and the students table is one-to-multiple. The state_lookup table defines a general set of abbreviated state abbreviations -- this occurs once in every state in 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. Many students come from the same State. one-to-many relationships are implemented between state_lookup and students.
The foreign key also ensures the integrity of the State field in the students table. Each student always has the abbreviated state name of a member in the state_lookup table.
The foreign key constraint is created in the subtable. Next, create a foreign key constraint on the students table. For the state field, refer to the primary key of the state_lookup table.
1. Create a table
Create Table students
(Student_id varchar2 (10) Not null,
Student_name varchar2 (30) not null,
College_major varchar2 (15) not null,
Status varchar2 (20) not null,
State varchar2 (2 ),
License_no varchar2 (30) 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 check Constraints
Alter table students
Add constraint ck_students_st_lic
Check (State is null and license_no is null) or
(State is not null and license_no is not null ));
5. Create a foreign key constraint
Alter table students
Add constraint fk_students_state
Foreign key (state) References state_lookup (State );

I. Four types of errors
The integrity rules are enforced during the deletion of parent table updates and during the insertion and update of child tables. The SQL statements affected by the integrity of the reference are:
The update operation of the parent-update parent table cannot update the state value in the state_lookup table to the value that is still in use in the students table, but not in the state_lookup table.
The delete operation on the parent-delete parent table cannot delete the state value in the state_lookup table. As a result, the students table is still in use, but the state_lookup table does not have this value.
The insert operation on the child-insert sub-table cannot insert the value of state that is not in the state_lokup table.
The child-update sub-Table update operation cannot update the value of state to the value of State not in the state_lookup table.
The following example describes four error types:
The structure and test data of the test table are as follows:
State_lookup
--------------------------------
State state description
CA California
NY New York
NC North Carolina Lina
---------------------------------
Students
--------------------------------------------------------------------------------
Student ID Student name college major status state license No
A101 John biology degree null
A102 Mary math/science degree null
A103 Kathryn history degree ca MV-232-13
A104 Steven biology degree ny MV-232-14
A105 William English 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: Maid (Scott. fk_students_state)
Violated-Child record found

2), parent-delete

SQL> Delete from state_lookup
2 Where State = 'CA ';

Delete from state_lookup
*
Error at line 1:
ORA-02292: Maid (Scott. fk_students_state)
Violated-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: Maid (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: Maid (Scott. fk_students_state)
Violated-parent key not found

The above four types of errors all have the same error code: ORA-02291
Integrity of reference is a key part of database design. There are very few tables that are neither the parent table nor the child table of other tables.

Ii. Cascade Deletion
The foreign key syntax has an option to specify cascading deletion features. This feature only applies to the deletion statement of the parent table.
With this option, a delete operation on the parent table will automatically delete all related sub-table records.
Use the delete cascade option to create a foreign key constraint, and then follow a delete statement to delete the records of California in the state_lookup table and all students with the California license in the students table.
Alter table students
Add constraint fk_students_state
Foreign key (state) References state_lookup (state)
On Delete cascade;
Execute the delete statement:
Delete from state_lookup where State = 'CA ';

Then, you can query the data in the students table, and there will be no records whose field state value is ca.
If the table has a foreign key association but the cascade deletion option is not used, the deletion operation will fail.

When defining a cascading deletion, consider the following:
1. Is cascading deletion suitable for this application? Deleting a customer account from a parent reference table should not be deleted
2. What is the defined chain? View the association between a table and other tables, consider the potential impact, the order of magnitude of One deletion, and what impact it will bring.

If you cannot perform cascade deletion, you can set the value of the foreign key field of the sub-table to null and use the on Delete set null statement (the foreign key field cannot be set with the not null constraint ).
Alter table students
Add constraint fk_students_state
Foreign key (state) References state_lookup (state)
On Delete set NULL;

3. Reference Field syntax structure
Create a foreign key constraint. The foreign key field references the primary key or unique constraint field of the parent table. In this case, do not specify the reference field name for the foreign key, as shown below:
Alter table students
Add constraint fk_students_state
Foreign key (state) References state_lookup;
If no reference field is specified, the default reference field is the primary key of the parent table.
If the foreign key field references the unique rather than the primary key field, you must specify the field name in the Add constraint statement.

Iv. Integrity of reference between different user modes and database instances
Foreign key constraints can be applied between different user modes and different database instances, but this is not expected from a database management point of view. It is appropriate to put an application in the same user mode. I will not detail it here

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 595436