Model 1:N relationships with foreign keys

• Implementing relationships with Foreign Keys

  • A Foreign Key (FK) points to the Primary key (PK) of another table.
  • Domain of FK must be equal to the domain of PK
  • Each value of FK must exist in PK of the other table (FK constraint or ‘REFERENTIAL INTEGRITY)
  • FKs are not actual keys.

• Specifying Foreign Keys

        CREATE TABLE manufacturers(
            name varchar(255) PRIMARY KEY
        );
  
        INSERT INTO manufacturers
        VALUES ('Ford'),('VW'), ('GM');
  
        CREATE TABLE cars(
            model varchar(255) PRIMARY KEY,
            manufacturer_name varchar(255) REFERENCES manufacturers (name)
        );
  
        INSERT INTO cars
        VALUES ('Ranger', 'Ford'), ('Beetle', 'VW');
  

• Specifying Foreign Keys to Existing Tables

        ALTER TABLE a
        ADD CONSTRAINT a_fkey FOREIGN KEY (b_id) REFERENCES b (id);
  

• How to Implement N:M-relationships

  • Create a table
  • Add foreign keys for every connected table
  • Add additional attributes

      CREATE TABLE affiliations (
          professor_id integer REFERENCES professors (id),
          organization_id varchar(256) REFERENCES organizations (id),
          functino varchar(256)
      );
    

  • No primary key

• Referential Integrity

  • A record referencing another table must refer to an existing record in that table.
  • Specified between two tables
  • Enforced through foreign keys

• Referential Integrity Violations

  Referential Integrity from table A to table B is violated….

  • … if a record in table B that is referenced from a record in table A i deleted.
  • … if a record in table A referencing a non-existing record from table B is inserted.
  • Foreing keys prevent violations!

• Dealing with violations

        CREATE TABLE a (
            id integer PRIMARY KEY,
            column_a varchar(64),
            ...,
            b_id integer REFERENCES b (id) ON DELETE NO ACTION
        );
  
        CREATE TABLE a (
            id integer PRIMARY KEY,
            column_a varchar(64),
            ...,
            b_id integer REFERENCES b (id) ON DELETE CASCADE
        );
  

  ON DELETE…

  • NO ACTION: Throw an error
  • CASCADE: Delete all referencing records
  • RESTRICT: Throw an error
  • SET NULL: Set the referencing column to NULL
  • SET DEFAULT: Set the referencing column to its default value
• TO LEARN MORE ABOUT VIOLATING REFERENTIAL INTEGIRTY: Visit: https://www.gatevidyalay.com/referential-integrity-constraint-violation/