Advanced Sql

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.

BackLink