Not able to alter index in Intermediate Book Store Indexes POSTGRESQL course

Hello,

i have an issue with the Intermediate Book Store Indexes project - https://www.codecademy.com/paths/design-databases-with-postgresql/tracks/how-do-i-make-sure-my-database-stays-fast/modules/indexes/projects/indexes-part-2-project - step 6.

I am not able to add the primary key for the customers table. It is not showing when i query the pg_indexes table and thus i’m not able to complete step 7.

my code is like this:

ALTER TABLE customers
  ADD CONSTRAINT customers_pkey
    PRIMARY KEY (customer_id);

and then i query

select *
from pg_indexes;

but i do not see the index that should have been created by the primary key query.

Any idea why this is?

Hello @fredehk,

Primary keys can only be set on columns that are not null. Try altering the table so the column is not null and then go back to create your primary key.

Hi @coffeencake,
Thanks for your reply!

I just checked the table and it has no null values in customer_id. So i believe i should still be able to make customer_id the primary key out of the box if i understand your proposal correctly

Oh, seems this was a non-issue.

I just did

alter table customers
add constraint customers_pkey
primary key (customer_id);

select *
from pg_indexes;

and now i see the index and can solve step 7.

1 Like

Hmmm, that’s interesting. I actually stepped thru the exercise and repeated your steps. The index did not get created until I made the customer_id column NOT NULL in the customers table.

I ran the following statement first and then created the primary keys.

ALTER TABLE customers
 ALTER COLUMN customer_id SET NOT NULL
 ;

I’m glad it’s working now :slight_smile:

1 Like

Thats weird. Anyhow thank you for your help :slight_smile:

2 Likes