您在這裡

Foreign Key Rules

19 十月, 2015 - 16:13

Additional foreign key rules may be added, such as what to do with the child rows (Orders table) when the record with the PK – the parent (Customer) is deleted or changed (updated). The relationship window in Access shows two additional options for foreign keys rules. Cascade Update and Cascade Delete. If they are not selected, the system would prevent the deletion or update of PK values in the parent table (Cus tomer) if a child record exists. The child record is any record with a matching PK.

DELETE

  • RESTRICT
  • CASCADE
  • SET TO NULL

UPDATE

  • RESTRICT
  • CASCADE

In some databases, an additional option exists when selecting the Delete option. That is ‘Set to Null’. In these situations, the PK row is deleted, but the FK in the child table is set to Null. Though this creates an orphan row, it is acceptable.

Enterprise Constraints – sometimes referred to as Semantic constraints. They are additional rules specified by users or database administrators. i.e. A class can have a maximum of 30 students. A teacher can teach a maximum of 4 classes a semester. An employee cannot take a part in more than 5 projects. Salary of an employee cannot exceed the salary of the employee’s manager.