Attribute Constraints

• Data that is entered into a row must be a certain attribute

Type CASTs

• CAST is to change the attribute to something else
• For example, if a number is saved as a string (‘5’), you can cast it onto an integer in order to create calculations

-- Calculate the net amount as amount + fee (CAST fee as integer)
SELECT transaction_date, amount + CAST(fee AS integer) AS net_amount 
FROM transactions;

Numeric data types

• ex. numeric(3,2)
  • numeric that has at precision of 3 and scale of 2
  • Total of 3 digits and 2 digits after decimal point
  • An example of numeric(3,2) is 1.23. 10.23 would not work

Alter table after table creation / USING function

-- ex. 1
ALTER TABLE students
ALTER COLUMN name
TYPE varchar(128);

-- ex. 2 Alter with rounding
ALTER TABLE students
ALTER COLUMN average_grade
TYPE integer
USING ROUND(average_grade);

Substring to limit characters

• Similar to truncating information
• Limit what is entered to the first __ characters

-- Convert values in firstname to a max of 16 characters
ALTER TABLE professors
ALTER COLUMN firstname
TYPE varchar(16)
USING SUBSTRING(firstname FROM 1 for 16)

Adding or Removing not-null constraint

-- Create table
CREATE TABLE students (
   ssn integer NOT NULL,
   lastname varchar(64) NOT NULL,
   home_phone integer,
   office_phone integer
);

-- After table has been created, set column to not null
ALTER TABLE students
ALTER COLUMN home_phone
SET NOT NULL;

-- Remove not-null
ALTER TABLE students
ALTER COLUMN home_phone
DROP NOT NULL;

Adding unique constraint after table is created

-- syntax to adding unique constraint to table
ALTER TABLE {table}
ADD CONSTRAINT {new_table_name} UNIQUE({existing_column});

Finding Unique Constraint

1. Find number of rows
2. Find combination of columns to get the number of rows

-- FIND total number of rows
SELECT COUNT(*) FROM professors;

-- Find combination of columns that will lead to all unique values
SELECT COUNT(DISTINCT(firstname, lastname))
FROM professors;

Key Constraints

Alter/Rename column name

-- Rename organization to id
ALTER TABLE organizations
RENAME COLUMN organization TO id;

Make id a primary key

• Primary key must be unique and not null

-- Alter column to be primary key
ALTER TABLE organizations
ADD CONSTRAINT organization_pk PRIMARY KEY (id);

Surrogate Key

• Adding an extra column that we’ll use as the primary
• Adding a surrogate key with serial data type

Adding a surrogate key with serial data type

-- Add surrogate key with serial data type
ALTER TABLE cars
ADD COLUMN id serial PRIMARY KEY
INSERT INTO cars 
VALUES ('Volkswagon', 'Blitz', 'black');

Insert information with serial data time

-- insert data with serial data constraint. Automatically populates
INSERT INTO cars
VALUES ('Opel', 'Astra', 'green')

Using a combination of two columns for surrogate key

-- Create Column
ALTER TABLE table_name
ADD COLUMN column_c varchar(256)

-- Concatenate two columns and set it to created column
UPDATE table_name
SET column_c = CONCAT(column_a, column_b);

-- Make column the primary key
ALTER table_name
ADD CONSTRAINT pk PRIMARY KEY (column_c);

Specifying foreign keys

• Foreign key constraint makes sure that anything being entered must match the column of the foreign key
• ALTER TABLE {table}
• ADD CONSTRAINT {name_of_foreign_key} ({id_column}) REFERENCES {table} ({id_column_of_other_table})

-- Create table
CREATE TABLE manufacturers (
   name varchar(255) PRIMARY KEY);

-- Add information
INSERT INTO manufacturers
VALUES ('Ford'), ('VW'), ('GM');

-- Create table with foreign key
CREATE TABLE cars (
   model varchar(255) PRIMARY KEY,
   manufacturer_name varchar(255) REFERENCES manufacturers (name));

-- Add data to cars
INSERT INTO cars
VALUES ('Ranger', 'Ford'), ('Beetle', 'VW');

-- Add constraint sytax
ALTER TABLE a
ADD CONSTRAINT a_fkey FOREIGN KEY (b_id) REFERENCES b (id); 

Implementing N:M-relationships

• Create column having 2 foreign keys from other tables

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

Update columns based on values in another table

-- Syntax to update columns based on another column
UPDATE {table}
SET {name_of_new_column} = {other_table}.{column_foreign_key}
FROM {other_table}
WHERE condition1;

Referential Constraints

• If you try to delete item in table b that is referenced in table a, an error will occur (foreign key constraint)
• Can also include ON DELETE NO ACTION to prevent deletion of

-- If deleting foreign key, take no action
CREATE TABLE a (
   id integer PRIMARY KEY,
   column_a varchar(64),
   b_id integer REFERENCES b (id) ON DELETE NO ACTION
);

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

Changing key constraint

Find the correct constraint name

-- Identify the correct constraint name
SELECT constraint_name, table_name, constraint_type
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY';

Drop the foreign keys constraint and add new constraint with different ON DELETE function

-- drop constraint first
ALTER TABLE affiliations
DROP CONSTRAINT affiliations_organization_id_fkey;

-- ADD new CASCADE ON DELETE 
ALTER TABLE affiliations
ADD CONSTRAINT aailiations_organization_id_fkey (organization_id) REFERENCES organziation (id) ON DELETE CASCADE;