Shulou(Shulou.com)06/01 Report--

Foreign key constraints guarantee referential integrity. Foreign key constraints limit the range of values for a column. One example is to limit the state name to be abbreviated in a finite set of values, which is another control structure-- a parent table.

Let's create a reference table that provides a complete list of state abbreviations, and then use referential integrity to ensure that students have the correct state abbreviations. The first table is the state reference table, with State as the primary key

The above four types of errors all have the same error code: ORA-02291

Referential integrity is a key part of database design. There are very few tables that are neither the parent nor the child of other tables.

II. Cascading deletion

Foreign key syntax has an option to specify cascading delete features. This feature applies only to delete statements of 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 to create a foreign key constraint, followed by a delete statement to delete the record of California in the state_ lookup table and all 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

Execute the delete statement:

DELETE FROM state_lookup WHERE state = 'CA'

Then query the data in the students table, and there are no records with the field state value of CA.

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

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

1. Is cascade deletion suitable for this application? Delete from outside of a parent reference table should not delete customer account

2. What is the defined chain? Look at the association of tables with other tables and consider the potential impact and the order of magnitude of a deletion and what impact it will have

If you cannot cascade deletes, you can set the foreign key field value of the child table to null and use the on delete set null statement (foreign key fields cannot set not null constraints).

ALTER TABLE students

ADD CONSTRAINT fk_students_state

FOREIGN KEY (state) REFERENCES state_lookup (state)

ON DELETE SET NULL

Third, reference field syntax structure

A foreign key constraint is created when the foreign key field references the primary key or Unique constraint field of the parent table. In this case, the foreign key reference field name may not be specified 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 refers to a Unique instead of a Primary Key field, you must specify the field name in the add constraint statement.

Referential integrity between different user modes and database instances

Foreign key constraints can be applied between different user modes and different database instances, but from a database management point of view, this is not desirable. It is more appropriate for an application to be placed in a user mode. I will not go into 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 parent table update deletion and child table insert updates. The SQL statement affected by referential integrity is that the PARENT-UPDATE parent table update operation cannot update the state value in the State_ lookup table to a value that is still in use in the students table but not in the State_ lookup table.

The PARENT-DELETE parent table delete operation cannot delete the state value in the State_ lookup table, resulting in the students table still in use while the state_ lookup table does not have this value.

The CHILD-INSERT child table insert operation cannot insert a value of state that is not in the state_llokup table. The CHILD-UPDATE child table update operation cannot update the value of state to the value of state that is not in the state_lookup table.

The following example illustrates four error types:

The structure of the test table 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 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: integrity constraint (SCOTT.FK_STUDENTS_STATE)

Violated per child record found

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

Then insert a few 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 Carolina')

We ensure referential integrity by implementing a parent-child relationship, as shown below

-Foreign key fields exist in the Students table

| | State_lookup | it is a 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 figure above shows the one-to-many relationship between the State_ Lookup table and the Students table, and the State_Lookup table defines a common collection of state abbreviations-once in each 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. There are many students from the same state, once implementing an one-to-many relationship of referential integrity between tables State_Lookup and Students.

The foreign key also ensures the integrity of the State field in the Students table. Every student always has a state abbreviation for the members of the State_ lookup table.

Foreign key constraints are created in child tables. Let's create 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 (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 Unique constraint

ALTER TABLE students

ADD CONSTRAINT uk_students_license

UNIQUE (state, license_no)

USING INDEX TABLESPACE student_index

4. Create Check constraint

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.