Keys and Superkeys

• What is a Key?

  • Attribute(s) that idenfity a record uniquely
  • As long as attributes can be removed: superkey
  • If no more attributes can be removed: minimal superky or key

• Primary Keys

  • One primary key per database table, chosen from candidate keys
  • Uniquely identifies records, e.g for referencing in other tables
  • Unique and not-null constraints both apply
  • Primary keys are time-invariant

  Example:

        CREATE TABLE products(
            product_no integer UNIQUE NOT NULL,
            name text,
            price numeric
        );
          
        CREATE TABLE products(
            product_no integer PRIMARY KEY,
            name text,
            price numeric
        );
          
        CREATE TABLE example(
            a integer,
            b integer,
            c integer,
            PRIMARY KEY (a,c)
        );
  

  Specifying Primary Keys

        ALTER TABLE table_name
        ADD CONSTRAINT some_name PRIMARY KEY (column_name)
  

• Surrogate Keys (a.k.a Synthetic Primary Key)

  • Surrogate Keys also called as a Synthetic Primary Key are the artifically created keys in order to uniquely identify a row in the table when there are no natural primay key in the table.
  • Primary Keys should be built from as few columns as possible
  • Primary Keys should never change over time

  Adding a surrogate key with serial data type

        ALTER TABLE cars
        ADD COLUMN id serial PRIMARY KEY;
  
        INSERT INTO cars
        VALUES ('Volkswagen', 'Blitz', 'black');
  

  Another type of surrogate key

        ALTER TABLE table_name
        ADD COLUMN column_c varchar(256);
  
        UPDATE table_name
        SET column_c = CONCAT(column_a, column_b);
        ALTER TABLE table_name
        ADD CONSTRAINT pk PRIMARY KEY (column_c);