Column Types and Constraints

Exploratory Data Analysis in SQL

Christina Maimone

Data Scientist

Column constraints

Foreign key: value that exists in the referenced column, or NULL
Primary key: unique, not NULL
Unique: values must all be different except for NULL
Not null: NULL not allowed: must have a value
Check constraints: conditions on the values
- column1 > 0
- columnA > columnB

Common

Special

Table of numeric data type names and descriptions

Fortune 500 databse table

Format

-- With the CAST function
SELECT CAST (value AS new_type);

Examples

-- Cast 3.7 as an integer
SELECT CAST (3.7 AS integer);

-- Cast a column called total as an integer
SELECT CAST (total AS integer)
  FROM prices;

Format

-- With :: notation
SELECT value::new_type;

Examples

-- Cast 3.7 as an integer
SELECT 3.7::integer;

-- Cast a column called total as an integer
SELECT total::integer
  FROM prices;

Exploratory Data Analysis in SQL