Better data quality with constraints

Introduction to Relational Databases in SQL

Timo Grossenbacher

Data Journalist

Integrity constraints

  1. Attribute constraints, e.g. data types on columns (Chapter 2)
  2. Key constraints, e.g. primary keys (Chapter 3)
  3. Referential integrity constraints, enforced through foreign keys (Chapter 4)
Introduction to Relational Databases in SQL

Why constraints?

  • Constraints give the data structure
  • Constraints help with consistency, and thus data quality
  • Data quality is a business advantage / data science prerequisite
  • Enforcing is difficult, but PostgreSQL helps
Introduction to Relational Databases in SQL

Data types as attribute constraints

From the PostgreSQL documentation.

Introduction to Relational Databases in SQL

Dealing with data types (casting)

CREATE TABLE weather (
 temperature integer,
 wind_speed text);

SELECT temperature * wind_speed AS wind_chill
FROM weather;

operator does not exist: integer * text
HINT: No operator matches the given name and argument type(s). 
You might need to add explicit type casts.

SELECT temperature * CAST(wind_speed AS integer) AS wind_chill
FROM weather;
Introduction to Relational Databases in SQL

Let's practice!

Introduction to Relational Databases in SQL

Preparing Video For Download...