Constraints

Basics

• Types of constraints:
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL
  • C: NOT NULL/CHECK
  • P: PK
  • R: FK
  • U: UNIQUE
• Parent/Child table
  • Child table:
    • the one with another table’s primary key as foreign key
    • has foreign key constraints, allows null, because it’s checked only when there is data entry
    • The ‘many’ side of one-to-many relationship
  • Parent table:
    • the one that the child table is referring to
    • The ‘one’ side of one-to-many relationship
• P10 Naming rules: tablename_columnname_constrainttype

*CHECK ALL CONSTRAINTS:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name
FROM user_constraints
WHERE table_name = ‘TABLENAME’;
*

“CONSTRAINT” KEYWORD IS OPTIONAL WHEN ADDING CONSTRAINT REQUIRED WHEN DROPPING COSTRAINT

P11 Creating constraints for new tables

• (table can be created without PK constraints)
• Column level
  • Required for NOT NULL constraints
  • (column1 datatype CONSTRAINT constraintname CONSTRAINTYPE)
• Table level
  • Required if a constriant is associated with multiple columns
    • e.g. composite PK
  • (CONSTRAINT constraintname CONSTRAINTYPE (column1, column2, …))
• P15 COnstraints are enforced on a row level: entire row is rejected if one constraint does not match
• AS is optional only when using as column alias

P17 Adding constraints to existing tables

• How:
  • ALTER TABLE tablename ADD CONSTRAINT constraintname PRIMARY KEY (column)
  • MODIFY ….. NOT NULL
• When creating a table from new table, onlny NOT NULL costraints are saved, PK is NOT saved

Adding FK Constraints

• When removing cinstraints, remove children first, then remove parents, because children need to reference parents, will result in an error
• Or use