Type_id contains null values – Build a Periodic Table Database Course

Backend Development
1

:wave:

I am currently working through the Build A Periodic Table course and am trying to get a green check by the " Your properties table should have a type_id foreign key column that references the type_id column from the types table. It should be an INT with the NOT NULL constraint"

My understanding is that I start by adding the column and then add the foreign key constraint. However, starting here:
ALTER TABLE properties ADD COLUMN type_id INT NOT NULL;

I am continuously receiving an error:
ERROR: column "type_id" contains null values

I tried resetting the course and starting with this step and continue to get the error.

The error seems to be telling me that there is data but when I list the table’s columns there is no type_id column:

Table "public.properties"
    Column     |         Type          | Collation | Nullable | Default 
---------------+-----------------------+-----------+----------+---------
 atomic_number | integer               |           | not null | 
 type          | character varying(30) |           |          | 
 weight        | numeric(9,6)          |           | not null | 
 melting_point | numeric               |           |          | 
 boiling_point | numeric               |           |          | 
Indexes:
    "properties_pkey" PRIMARY KEY, btree (atomic_number)
    "properties_atomic_number_key" UNIQUE CONSTRAINT, btree (atomic_number)

I’m scratching my head. Any thoughts or insight here would be greatly appreciated.

Thank you,
Richard
​ ​ ​ ​

1 Like
2

This one is a little tricky and could probably use a change or a hint. You can’t add the NOT NULL when you create the column because all the existing rows in the table will have that field as null - so it gives an error. For now, you should add the column without the NOT NULL - then fill in all the fields for that column, then set it to not null.

6 Likes
3

AHA! This makes perfect sense now. I wasn’t thinking about the existing data (rows) in the table. You are right, adding a column that constrains to NOT NULL to this table would be impossible because NULL values would exist automatically.

I was able to go through and get the green check by creating the table and then the data and then add the NOT NULL constraint.

Thank you @moT01

1 Like
4

Hey I know this might be solved and its bit old but I wanted to know how you approach this if you had to add several thousand type_id?

Like how’d you automatically add this values to the properties table from the types table automatically?

Guess you’d had to create a type_id column and fill it with thousand type_id values from the types table…

5

I have not tried it yet. I suppose we can make it using a script