Guard Customer Data

Easy

Description

Add two rules to customers:

no two customers may share a phone number. Name this rule uq_customers_phone.
loyalty_points can be at most 100000. Name this rule chk_loyalty_max.

Customers without a phone number must still be allowed.

Then list the table's unique and check rules with this query:

Code
SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'customers'::regclass AND contype IN ('u', 'c')
ORDER BY conname;

Finish with exactly this query.

Tables

customers
customer_id (PK)
full_name
email
phone
city
country
date_of_birth
loyalty_points
created_at

Description

conname	definition
chk_loyalty_max	CHECK ((loyalty_points <= 100000))
customers_date_of_birth_check	CHECK ((date_of_birth < CURRENT_DATE))
customers_email_key	UNIQUE (email)
customers_loyalty_points_check	CHECK ((loyalty_points >= 0))
uq_customers_phone	UNIQUE (phone)

Add two rules to customers:

no two customers may share a phone number. Name this rule uq_customers_phone.

loyalty_points can be at most 100000. Name this rule chk_loyalty_max.

Customers without a phone number must still be allowed.

Then list the table's unique and check rules with this query:

Code
SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'customers'::regclass AND contype IN ('u', 'c')
ORDER BY conname;

Finish with exactly this query.

customers

customer_id (PK)

full_name

phone

city

country

date_of_birth

loyalty_points

created_at

Description

Add two rules to customers:

no two customers may share a phone number. Name this rule uq_customers_phone.

loyalty_points can be at most 100000. Name this rule chk_loyalty_max.

Customers without a phone number must still be allowed.

Then list the table's unique and check rules with this query:

Code
SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'customers'::regclass AND contype IN ('u', 'c')
ORDER BY conname;

Finish with exactly this query.

customers

customer_id (PK)

full_name

phone

city

country

date_of_birth

loyalty_points

created_at

conname	definition
chk_loyalty_max	CHECK ((loyalty_points <= 100000))
customers_date_of_birth_check	CHECK ((date_of_birth < CURRENT_DATE))
customers_email_key	UNIQUE (email)
customers_loyalty_points_check	CHECK ((loyalty_points >= 0))
uq_customers_phone	UNIQUE (phone)

conname

definition

chk_loyalty_max

CHECK ((loyalty_points <= 100000))

customers_date_of_birth_check

CHECK ((date_of_birth < CURRENT_DATE))

customers_email_key

UNIQUE (email)

customers_loyalty_points_check

CHECK ((loyalty_points >= 0))

uq_customers_phone

UNIQUE (phone)