title: 10-Advanced-SQL
date: 2023-12-27	
status: DONE
tags:
  - MySQL
  - NOTE
  - Lec10
author:
  - AllenYGY
created: 2023-12-27T21:06
updated: 2024-03-21T21:51
publish: True

Advanced-SQL

Constraints

Integrity Constraints
Check Clauses
Referential Integrity
Keys and Referential Integrity
Cascading actions in referential integrity
Assertions

Integrity Constraints

Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency.

Example:
- gpa must be from 0.0 to 4.0

ALTER TABLE STUDENT
ADD CONSTRAINT gpa_domain
CHECK(gpa>=0.00 AND gpa<=4.00)

Referential Integrity

Referential integrity ensures that a value that appears in one relation table for a given set of attributes must also appears in the corresponding set of attributes in the other relation table

关联删除
关联更新

Example:

CREATE TABLE student (
  …
  p_code INT(11),
  FOREIGN KEY (p_code) REFERENCES program(p_code),
  …
)

CREATE TABLE program (
  …
  p_code INT(11) NOT NULL,
  PRIMARY KEY (p_code),
  …
)

ALTER TABLE student
  ADD CONSTRAINT student_program 
    FOREIGN KEY (p_code) REFERENCES program(p_code) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE

Alternative cascade actions can be:
- SET NULL and SET DEFAULT.