Database-MySQL [3i Infotech Placement]: Sample Questions 147 - 147 of 162

Glide to success with Doorsteptutor material for competitive exams : get questions, notes, tests, video lectures and more- for all subjects of your exam.

Question 147

Edit

Describe in Detail

Essay▾

Define the “integrity rules”

Explanation

There are two Integrity rules.

Entity Integrity:

  • Entity Integrity is the mechanism the system provides to maintain primary keys.
  • The primary key serves as a unique identifier for rows in the table.
  • Entity Integrity ensures two properties for primary keys:
    • The primary key for a row is unique; it does not match the primary key of any other row in the table.
    • The primary key is not null, no component of the primary key may be set to null.
  • The uniqueness property ensures that the primary key of each row uniquely identifies it; there are no duplicates.
  • The second property ensures that the primary key has meaning, has a value; no component of the key is missing.

Referential Integrity:

  • Referential Integrity is the mechanism the system provides to maintain foreign keys.
  • The definition of a foreign key must specify the table whose primary key is being referenced.
    • Referential Integrity ensures only one property for foreign keys:
  • A valid foreign key value must always reference an existing primary key or contain a null
  • While the Referential Integrity property looks simpler than those for Entity Integrity, the consequences are more complex since both primary and foreign keys are involved.
  • The rule for foreign keys is:
  • No operation (INSERT, UPDATE) can create a non-null foreign key unless a corresponding primary key exists.
    • Any operation that produces a non-null foreign key value without a matching primary key value is rejected.
    • Primary keys are also constrained by Referential Integrity:
  • No operation (UPDATE, DELETE) can remove or change a primary key while a referencing foreign keys exist.

Developed by: