Https://www.codecademy.com/paths/full-stack-engineer-career-path/tracks/fscp-designing-relational-databases/modules/fscp-database-normalization/projects/database-normalization-at-freds-furniture

for the task 8 in this project when i try to defined item_id as primary key I receive this kind of "ERROR: could not create unique index “items_pkey”
DETAIL: Key (item_id)=(34) is duplicated.
this is my code for these tasks :

SELECT customer_id, customer_email, customer_phone

FROM store

WHERE customer_id = 1;

SELECT item_1_id, item_1_name, item_1_price

FROM store

WHERE item_1_id = 4;

CREATE TABLE customers AS SELECT DISTINCT customer_id, customer_phone, customer_email

FROM store;

ALTER TABLE customers

ADD PRIMARY KEY (customer_id);

CREATE TABLE items AS SELECT DISTINCT item_1_id AS item_id, item_1_name AS item_name, item_1_price AS item_price

FROM store

WHERE item_1_id IS NOT NULL

UNION 

SELECT DISTINCT item_2_id AS item_id, item_2_name AS item_name, item_2_price AS item_price

FROM store

WHERE item_2_id IS NOT NULL

UNION ALL

SELECT DISTINCT item_3_id AS item_id, item_3_name AS item_name, item_3_price AS item_price

FROM store

WHERE item_3_id IS NOT NULL;

ALTER TABLE items

ADD PRIMARY KEY (item_id);

Hello!

Primary key must contains only unique values. But as result of your query table items populated with some duplicate items.

I recommend to read about differences between UNION and UNION ALL (see, for example, PostgreSQL: Documentation: 14: 7.4. Combining Queries (UNION, INTERSECT, EXCEPT)). And figure out which of it is correct choice for this query.

1 Like

Thank you very much my friend

1 Like

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.