Constraints

• Integrity Constraints

  • Attribute Constraints, e.g data types on columns
  • Key Constraints, e.g primary keys
  • Referential Integrity Constraints, enforced through foreign keys

• Why Constraints?

  • Constraints give the data structure
  • Constraints help with consistency, and thus data quality
  • Data quality is a business advantage / data science prerequisite

• Data Types as Attribute Constraints

  

• Casting Data Types

        CREATE TABLE weather(
            temperature integer,
            wind_speed text
        );
          
        SELECT temperature * wind_speed AS wind_chill
        FROM weather;
  

  The above sql statement results in error. Operator does not exist: integer * text
  HINT: No operator matches the given name and argument type(s).
  You might need to ass explicit type casts.

        SELECT
            temperature * CAST(wind_speed AS integer) AS wind_chill
        FROM weather;
  

• Most Common Data Types

  • text: character strings of any length
  • varchar[(x)]: a maximum of n characters
  • char[(x)]: a fixed-length string of n characters
  • boolean: can only take three states, e.g: TRUE, FALSE and NULL
  • date, time and timestamp: various formats for date and time calculations
  • numeric: arbitray precision numbers, e.g: 3.1457
  • integer: whole numbers in the range of -2147483648 and +2147483647

• Creating columns with specific data types upon table creation

  •       CREATE TABLE students (
              ssn integer,
              name varchar(64),
              dob date,
              average_grade numeric(3,2), --e.g 5.54
              tuition_paid boolean
          );
    

  • Alter types after table creation

          ALTER TABLE students
          ALTER COLUMN name
          TYPE varchar(128);
    

          ALTER TABLE students
          ALTER COLUMN average_grade
          TYPE integer
              -- Turns 5.54 into 6, not 5, before type conversion
          USING ROUND(average_grade)
    

• Not-Null Constraints

  • Disallow NULL values in a certain column
  • Must hold true for the current state
  • Must hold true for any future state

• Adding a Not-Null Constraints

  • While creating a table

      CREATE TABLE students (
          ssn integer not null,
          lastname varchar(54) not null,
          home_phone integer,
          office_phone integer
      );
    

  • After the table has been created

      ALTER TABLE students
      ALTER COLUMN home_phone
      SET NOT NULL;
    

• Removing a Not-Null Constraints

        ALTER TABLE students
        ALTER COLUMN ssn
        DROP NOT NULL:
  

• Unique Constraints

  • Disallow duplicate values in a column
  • Must hold true for the current state
  • Must hold true for any future state

• Adding UNIQUE Constraints

  • While creating a table

      CREATE TABLE table_name (
          column_name UNIQUE
      )
    

  • After the table has been created

      ALTER TABLE table_name
      ADD CONSTRAINT some_name UNIQUE(column_name);