Domain types for validation in PostgreSQL - Mini Project: Build & Apply

Using Domain Types for Validation in PostgreSQL

📖 Scenario: You are creating a simple database for a small bookstore. You want to ensure that certain fields like ISBN numbers and book prices follow specific rules to keep your data clean and valid.

🎯 Goal: Build a PostgreSQL database schema that uses domain types to validate ISBN numbers and book prices automatically.

📋 What You'll Learn

Create a domain type called isbn_type that only allows 13-character strings.

Create a domain type called price_type that only allows numeric values greater than 0.

Create a table called books with columns id, title, isbn using isbn_type, and price using price_type.

💡 Why This Matters

🌍 Real World

Domain types help keep data clean by automatically checking values when inserting or updating records, reducing errors in real-world databases like bookstores or libraries.

💼 Career

Understanding domain types is useful for database developers and administrators to enforce business rules at the database level, improving data quality and reliability.

1

Create the isbn_type domain

Create a domain called isbn_type as varchar(13) that checks the length of the string is exactly 13 characters.

PostgreSQL

-- Your code here to create the isbn_type domain

Need a hint?

Use CREATE DOMAIN with a CHECK constraint that uses char_length(VALUE) = 13.

2

Create the price_type domain

Create a domain called price_type as numeric that checks the value is greater than 0.

PostgreSQL

CREATE DOMAIN isbn_type AS varchar(13) CHECK (char_length(VALUE) = 13);
-- Your code here to create the price_type domain

Need a hint?

Use CREATE DOMAIN with a CHECK constraint that ensures VALUE > 0.

3

Create the books table

Create a table called books with columns: id as serial PRIMARY KEY, title as varchar(100), isbn using the isbn_type domain, and price using the price_type domain.

PostgreSQL

CREATE DOMAIN isbn_type AS varchar(13) CHECK (char_length(VALUE) = 13);
CREATE DOMAIN price_type AS numeric CHECK (VALUE > 0);
-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with the specified columns and domain types.

4

Add a constraint to ensure title is not null

Alter the books table to add a NOT NULL constraint on the title column.

PostgreSQL

CREATE DOMAIN isbn_type AS varchar(13) CHECK (char_length(VALUE) = 13);
CREATE DOMAIN price_type AS numeric CHECK (VALUE > 0);
CREATE TABLE books (
    id serial PRIMARY KEY,
    title varchar(100),
    isbn isbn_type,
    price price_type
);
-- Your code here to add NOT NULL constraint on title

Need a hint?

Add NOT NULL after the title varchar(100) column definition.