Database integrity – entity integrity, referential integrity, attribute constraint naming clauses, domains

Article directory

• Database integrity
• • 1. Entity integrity
  • • 1. Define entity integrity
    • 2. Entity integrity check and breach handling
  • 2. Referential integrity
  • • 1. Define referential integrity
    • 2. Reference integrity check and default conditions
  • 3. Constraints on attributes
  • • 1. Definition of constraints on attributes
    • 2. Inspection of constraints on attributes and handling of breaches
  • 4. Integrity constraint naming clause
  • • 1. Definition of integrity naming clause
    • 2. Modify the integrity restrictions in the table
  • 5. Domain

Database integrity

Prevent the existence of semantically inconsistent data in the database, that is, prevent the existence of incorrect data in the database;
What to protect against: Unsemantic, incorrect data

• To maintain the integrity of the database, the DBMS must:
  1. A mechanism to provide integrity constraints;
  2. Provide methods for integrity checking;
  3. Breach of contract handling;

1. Entity integrity

1. Define entity integrity

Entity integrity of the relational model
Define with PRIMARY KEY when CREATE TABLE

There are two ways to define codes composed of single attributes:

• Defined as a column-level constraint that is, defined after the attribute

For example: define the Sno attribute in the Student table as code

CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY
Sname CHAR(4) ,
Ssex CHAR(2) ,
Sage SMALLINT,
Step CHAR(2)
);

• Defined as table-level constraints That is, declare who is the code at the end

For example: define the Sno attribute in the Student table as code

CREATE TABLE Student(
Sno CHAR(9),
Sname CHAR(4) ,
Ssex CHAR(2) ,
Sage SMALLINT,
Step CHAR(2),
PRIMATY KEY(Sno) //Defined as table-level integrity constraints
);

There is only one way to define a code composed of multiple attributes:
Defined as table-level constraints

For example: define sno and cno in the SC table as codes

CREATE TABLE SC(
Sno CHAR(9) ,
Cno SMALLINT ,
Grade SMALLINT,
PRIMARY KEY(sno,cno) //Define table-level integrity
);```

2. Entity integrity check and breach handling

When inserting or updating the main key column, the DBMS automatically checks the entity integrity, including:
1. Check whether the main code value is unique. If it is not unique, then the insertion or modification will be refused;
2. Check whether each attribute of the main code is empty. If it is empty, then the insertion or modification will be refused;
The operations to check whether the primary code is unique include full table scan and index;

2. Referential integrity

Entity integrity is the complete definition of the main attributes for a single table, while referential integrity is compared to two tables.

1. Define referential integrity

When CREATE TABLE, use FOREIGN KEY to define which columns are foreign keys, and use PEFERENCES to indicate which tables’ main keys these foreign keys refer to;

Speaking of this, the definition of foreign code is mentioned. Foreign code is relative to the table. It is not the code of the table, but the code of other tables;

For example: Define referential integrity in SC tables

CREATE TABLE SC(
Sno CHAR(9) ,
Cno SMALLINT ,
Grade SMALLINT,
PRIMARY KEY(sno,cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

2. Reference integrity check and default conditions

Referential integrity, as the name suggests, is “reference”, that is to say, the sno in the reference table (sc) table refers to the sno in the referenced table (student table), so the sno in the sc table must be in the referenced table (student table) existing in;
When you want to insert or modify a course selection record, sno must exist;

For example: when you want to insert a course selection record whose student number does not exist in the student table

INSERT INTO sc VALUES('200215126',3,80)

Result:

For the referenced table, when you delete the tuples of the referenced table or modify the primary key value, referential integrity will also be destroyed;
Processing method:

1. Deny execution (NO ACTION) – default policy

CREATE TABLE SC(
Sno CHAR(9) ,
Cno SMALLINT ,
Grade SMALLINT,
PRIMARY KEY(sno,cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno), //The default is NO ACTION
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

DELETE FROM student WHERE SNO='200215121';

result:

UPDATE student SET sno='200215126'WHERE Sno='200215121';

Result:

2. Cascade operation (ON DELETE CASCADE ON UPDATE CASCADE)

CREATE TABLE SC(
Sno CHAR(9) ,
Cno SMALLINT ,
Grade SMALLINT,
PRIMARY KEY(sno,cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

After modifying the code value of the referenced table student or deleting the tuple, there will be a cascade operation in the sc table, and the corresponding data in the sc table will also be deleted;

3. Set NULL
After the value of the referenced table (student) is deleted, the corresponding value of the reference table (sc) is set to a null value;
After updating the value of the referenced table (student), the corresponding value of the reference table (sc) is set to a null value;

Summary:

3. Constraints on attributes

1. Definition of constraints on attributes

Defined when CREATE TABLE:

• NOT NULL -list value is not empty
• UNIQUE -List values are unique
• CHECK – Check whether a column value satisfies an expression

2. Checking and violating the constraints on attributes

When inserting a tuple or modifying an attribute, the DBMS automatically checks whether the constraints on the attribute are met. If not, the operation refuses to be executed;

• Column does not allow null values
  For example: The name, gender, age, and department of the student table cannot take null values.

CREATE TABLE Student(
Sno CHAR(9),
Sname CHAR(4) NOT NULL,
Ssex CHAR(2) NOT NULL,
Sage SMALLINT NOT NULL,
Step CHAR(2) NOT NULL,
PRIMATY KEY(Sno) //Defined as table-level integrity constraints
);

• Get unique values from column
  For example: the course name of the curriculum must have a unique value;

CREATE TABLE Course(
Cno SMALLINT PRIMARY KEY,
Cname VARCHAR(10) UNIQUE, //Get unique value
Cpno SMALLINT,
Ccredit SMALLINT '
)COMMENT'Course Schedule';

• Use the CHECK phrase to specify conditions that column values should meet;
  For example: the gender of the student table can only be male or female;

CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY
Sname CHAR(4) ,
Ssex CHAR(2) CHECK(Ssex IN('male','female')) ,
Sage SMALLINT,
Step CHAR(2)
);

4. Integrity constraint naming clause

1. Definition of integrity naming clause

Integrity constraints can be named when CREATE TABLE, thereby flexibly adding or deleting an integrity constraint

CONSTRAINT
Integrity constraints include:

• NOT NULL
• UNIQUE
• CHECK
• PRIMARY KEY
• FOREIGN KEY

2. Modify the integrity restrictions in the table

For example: Change the range of student numbers in the student table to 111111111-999999999, and change the age range from less than 30 years old to less than 40 years old.

ALTER TABLE student DROP CONSTRAINT C1;
ALTER TABLE student ADD CONSTRAINT C1 CHECK(Sno BETWEEN 11111111 AND 999999999);

ALTER TABLE student DROP CONSTRAINT C3;
ALTER TABLE student ADD CONSTRAINT C3 CHECK(Sage<40);

5. Domain

Domain is equivalent to the data type in C++. Of course, we know that a class is equivalent to a custom data type. In SQL, we can also define a domain ourselves, thus defining a custom data type;
SQL supports the concept of domain, and can create a domain and the integrity constraints that the domain should satisfy through the CREATE DOMAIN statement. When the integrity constraints on the domain change, you only need to modify the definition of the domain. Yes, without having to modify individual attributes on the domain;
For example: create a gender field and declare the value range of the gender field.

CREATE DOMAIN SexDomain CHAR(2) CHECK(VALUE IN('male','female');

In this way, Ssex in the student table can be written as

Ssex SexDomain;