FAQ: PostgreSQL Constraints - PostgreSQL Constraints Review

This community-built FAQ covers the “PostgreSQL Constraints Review” exercise from the lesson “PostgreSQL Constraints”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Design Databases With PostgreSQL

FAQs on the exercise PostgreSQL Constraints Review

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!
You can also find further discussion and get answers to your questions over in Language Help.

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head to Language Help and Tips and Resources. If you are wanting feedback or inspiration for a project, check out Projects.

Looking for motivation to keep learning? Join our wider discussions in Community

Learn more about how to use this guide.

Found a bug? Report it online, or post in Bug Reporting

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

Hello, please can someone help me out.
I am trying to add a not null constraint on the organization column of the speakers table, but it doesn’t work even though there are some rows with no values in them.

1 Like

The blanks in the organization column of the speakers table seem to be empty strings (not NULL). Before your UPDATE statement, let’s try:

SELECT * FROM speakers
WHERE organization IS NULL;

and

SELECT * FROM speakers
WHERE organization = '';
1 Like

Thank you. They are empty and not null.
Then all I need to do now is just add the null constraint. Or is there a need to still update the empty string to a default value like ‘NA’ as I was trying to do.

1 Like

Please I am getting errors when I try to add a foreign key to the registration table, it is supposed to reference the attendees table which is empty (I’m not sure if that is the cause of the error). I am also getting errors when adding a default value to the attendees table. Could you please review the entire code for me too. I’d be very grateful.
SPOILERS FOR ANYONE WHO HASN’T NOT DONE IT

-- SELECT * FROM speakers; -- SELECT * FROM talks; -- SELECT * FROM registrations LIMIT 10; SELECT * FROM attendees; -- SPEAKERS CONSTRAINTS UPDATE speakers SET organization = 'NA' WHERE organization = ''; ALTER TABLE speakers ALTER COLUMN organization SET NOT NULL; ALTER TABLE speakers ADD CHECK (years_in_role > 0 AND years_in_role < 50); ALTER TABLE speakers ALTER COLUMN name SET NOT NULL; ALTER TABLE speakers ADD UNIQUE (email); ALTER TABLE speakers ADD PRIMARY KEY (id); -- REGISTRATIONS CONSTRAINTS ALTER TABLE registrations ADD PRIMARY KEY (id); ALTER TABLE registrations ALTER COLUMN attendee_id SET NOT NULL; ALTER TABLE registrations ADD FOREIGN KEY (attendee_id) REFERENCES attendees(id); -- source of error ALTER TABLE registrations ALTER COLUMN session_timeslot SET NOT NULL; ALTER TABLE registrations ALTER COLUMN talk_id SET NOT NULL; ALTER TABLE registrations ADD FOREIGN KEY (talk_id) REFERENCES registrations(id); ALTER TABLE registrations ADD CHECK (date_part('year', session_timeslot) = 2020 AND date_part('month', session_timeslot) = 8); -- TALKS CONSTRAINTS ALTER TABLE talks ADD PRIMARY KEY (id); ALTER TABLE talks ALTER COLUMN title SET NOT NULL; ALTER TABLE talks ALTER COLUMN speaker_id SET NOT NULL; ALTER TABLE talks ALTER COLUMN session_timeslot SET NOT NULL; ALTER TABLE talks ADD FOREIGN KEY (speaker_id) REFERENCES speakers (id); -- ATTENDEES CONSTRAINTS ALTER TABLE attendees ADD PRIMARY KEY (id); ALTER TABLE attendees ALTER COLUMN name SET NOT NULL; ALTER TABLE attendees ALTER COLUMN name TYPE varchar(12); ALTER TABLE attendees ADD CONSTRAINT DF_standard_tickets DEFAULT 0 FOR standard_tickets_reserved; -- source of error ALTER TABLE attendees ADD CONSTRAINT DF_vip_tickets DEFAULT 0 FOR vip_tickets_reserved; -- source of error ALTER TABLE attendees ALTER COLUMN total_tickets_reserved SET NOT NULL; ALTER TABLE attendees ALTER COLUMN standard_tickets_reserved SET NOT NULL; ALTER TABLE attendees ALTER COLUMN vip_tickets_reserved SET NOT NULL; ALTER TABLE attendees ADD CHECK (standard_tickets_reserved + vip_tickets_reserved = total_tickets_reserved); INSERT INTO attendees VALUES (0, 'Attah Adwoa', 2, 2, 0); -- UPDATE speakers -- SET name = 'Lance Moss' -- WHERE id = 6; -- SELECT * FROM speakers; -- SELECT * FROM talks; -- SELECT * FROM registrations; SELECT * FROM attendees;

sql_error3

1 Like

There seem to be two problems when adding the foreign key attendee_id to the registrations table.

  1. There is no unique constraint to the id column in attendees table (this is what the first error message says).
  2. There is no row in the attendees table which corresponds to each value of attendee_id in the registrations table (this is a problem you suspected).

To resolve 1, do the following before adding the foreign key:

ALTER TABLE attendees ADD PRIMARY KEY (id);

But even if you do this, you will run into the problem 2. You will get the following error message:

ERROR:  insert or update on table “registrations” violates foreign key constraint “registrations_attendee_id_fkey”
DETAIL:  Key (attendee_id)=(17) is not present in table “attendees”.

I think the only workaround for this is to actually add corresponding rows to the attendees table.

For subsequent errors, I’m still not familiar with adding the default value. I may comment again when I learn about it later.

1 Like

Continued from my previous post.

For errors related to setting default values, this Stack Overflow Q & A may help:

Apparently the kind of syntax you wrote is in MS SQL Server, but in PostgreSQL it seems to cause a syntax error. You can use the following syntax instead:

ALTER TABLE attendees ALTER COLUMN standard_tickets_reserved SET DEFAULT 0;
ALTER TABLE attendees ALTER COLUMN vip_tickets_reserved SET DEFAULT 0;

In addition, it didn’t cause an error, but I thought the next line in your script was weird:

ALTER TABLE registrations ADD FOREIGN KEY (talk_id) REFERENCES registrations(id);

Isn’t it the id in the talks table:

ALTER TABLE registrations ADD FOREIGN KEY (talk_id) REFERENCES talks(id);

Like 1 in the previous post, this requires to set a unique constraint to the id column in advance:

ALTER TABLE talks ADD PRIMARY KEY (id);

Though my comments are mainly about the error-related parts, it was a good for me to read your script. It gave me an opportunity to learn some new things. The date_part function and setting the default value were new for me. Thank you!

1 Like

Thank you very much. Sorry for the late reply. Please if I get you the registration table must already have values where the attendee_id has id values in attendees. So unless I add values to the attendees before and make the id in attendee a primary key? Thank you.

1 Like

Thank you the changes work fine, and I think the take away is the syntax for the default values. And also to add a primary key before referencing it as a foreign key.
Much appreciation for taking time to review my code. What path are you on, and how far are you please? Maybe we could pair program.

1 Like

A post was split to a new topic: Off-Platform Project