Quartz 5

Database Constraints

Properties1
tagsdatabase/postgres, learningplatform/coursera, scriptinglanguage/sql

2 min read

Database Constraints

Keys

There are three kinds of Keys

  • PRIMARY - generally used to auto increment a field
    1. Never use LOGICAL keys as the primary key
    2. LOGICAL keys can and do change, albeit slowly, such as username and email_id
    3. Relationships that are based on string matching are less efficient and are slower than those based on integer
  • LOGICAL - what the application/outside world uses for lookup such as username, email-id
  • FOREIGN - generally an integer pointing to a row in another table, it points to a primary key in another table
    1. Always have the attribute or the column that is a pointing to another table in the following format _<table_name>id this enables the reader to understand which table the foreign key is pointing to

PRIMARY KEY can be easily created with the SERIAL keyword in the column which one would want to have be made as a primary key such as

CREATE TABLE users(
id SERIAL,
name VARCHAR(128),
email VARCHAR(128) UNIQUE,
PRIMARY KEY(id),
);

in the above command the column id will be created a PRIMARY KEY and an INDEX is automatically created on this column.

The UNIQUE constraint is a logical key which is used to make sure they are no duplicates in the column, this column is also auto-indexed.

On Delete Constraint

We use this constraint when we’d like to avoid zombie data or zombie data links, this directs what should happen to the child data when the parent data is deleted.

There are three options

  • Default/RESTRICT - Don’t allow changes that break the constraint.
  • CASCADE - Adjust child rows by removing or updating to maintain consistency
  • SET NULL - Set the foreign key columns in the child to null when the parent data is deleted

Graph View