PostgreSQL PGCES-02 Exam - Topic 4 Question 54 Discussion

Actual exam question for PostgreSQL's PGCES-02 exam

Question #: 54
Topic #: 4

You want to set a constraint so that the "item_id" in the "sales" table will always have a value that already exists as "id" in the "item_master" table. Select the correct SQL statement to fill in the underlined blank of the "sales" table. Definitions: CREATE TABLE item_master ( id INTEGER PRIMARY KEY, name TEXT ); CREATE TABLE sales ( sales_id INTEGER, item_id INTEGER, num INTEGER, );

AFOREIGN KEY (id) REFERENCES item_master (item_id)

BFOREIGN KEY (item_id) REFERENCES item_master (id)

CREFERENCES item_master (item_id)

DREFERENCES item_master (id)

EREFERENCES item_master (id) TO item_id

Show Suggested Answer

Suggested Answer: A, E

by Marge at Jan 23, 2024, 05:09 PM

Limited Time Offer

25%

Off

Get Premium PGCES-02 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Corinne

6 months ago

Surprised that E is so misleading!

upvoted 0 times

...

Phyliss

6 months ago

I thought it was C at first, but B is right.

upvoted 0 times

...

Hailey

6 months ago

Wait, why is A even an option?

upvoted 0 times

...

Casie

7 months ago

Totally agree, B makes sense.

upvoted 0 times

...

Vicky

7 months ago

B is the correct choice!

upvoted 0 times

...

Rhea

7 months ago

I definitely remember that the foreign key needs to reference the primary key, so I think B is the right choice since it links "item_id" to "id".

upvoted 0 times

...

Reena

7 months ago

I'm a bit confused about the syntax here. I feel like it could be either B or D, but I can't recall the exact details.

upvoted 0 times

...

Reena

7 months ago

I remember practicing a similar question, and I think the correct answer is B because it matches the "item_id" in sales to the "id" in item_master.

upvoted 0 times

...

Lorrine

8 months ago

I think the foreign key should reference the "id" in the "item_master" table, but I'm not sure which option that is.

upvoted 0 times

...

Mitzie

8 months ago

I'm pretty confident about this one. The "item_id" in the "sales" table needs to be a foreign key that references the "id" column in the "item_master" table, so the correct answer is option B.

upvoted 0 times

...

Lashaunda

8 months ago

This is a straightforward foreign key constraint question. The "item_id" in the "sales" table needs to reference the "id" column in the "item_master" table, so the correct answer is option B.

upvoted 0 times

...

Jacqueline

8 months ago

Okay, I think I've got this. The key is to match the "item_id" column in the "sales" table to the "id" column in the "item_master" table. So the correct answer is option B.

upvoted 0 times

...

Glen

8 months ago

Hmm, this looks like a tricky one. I'll need to carefully read through the table definitions and think about the relationship between the two tables.

upvoted 0 times

...

Shalon

8 months ago

I'm a bit confused here. The question is asking about setting a constraint, but the options seem to be about referencing columns. I'll need to double-check my understanding of foreign keys and referential integrity.

upvoted 0 times

...

Tandra

8 months ago

I'm pretty confident this is an A. Deduplicating the data is the best way to ensure no one gets the same email twice.

upvoted 0 times

...

Ligia

8 months ago

Okay, let's see. A digital network should provide secure data transport, so I'm leaning towards option C. But I'll double-check the other options just to be sure.

upvoted 0 times

...

Myra

8 months ago

I think since the maximum is set to 8, the system might just remain at 8, but I can't recall if it adds beyond that.

upvoted 0 times

...

Millie

1 year ago

I bet the correct answer is 'ALTER TABLE sales ADD CONSTRAINT item_id_fkey FOREIGN KEY (item_id) REFERENCES item_master(id)'. I mean, who doesn't love a little extra SQL syntax to spice things up?

upvoted 0 times

...

Ahmad

1 year ago

I wonder if the exam writer is a secret comedian. 'FOREIGN KEY (id) REFERENCES item_master (item_id)' - that's like trying to reference the moon by its serial number!

upvoted 0 times

Thora

11 months ago

Haha, yeah, that would be like referencing the moon by its serial number!

upvoted 0 times

...

Crissy

11 months ago

E) REFERENCES item_master (id) TO item_id

upvoted 0 times

...

Kanisha

11 months ago

That's the correct SQL statement to set the constraint.

upvoted 0 times

...

Josphine

11 months ago

B) FOREIGN KEY (item_id) REFERENCES item_master (id)

upvoted 0 times

...

Darell

1 year ago

Ah, the joys of database design! I'm going to have to think this one through carefully. Maybe I'll just ask the database gods for a little guidance.

upvoted 0 times

Jamie

11 months ago

Yes, that way we ensure that the 'item_id' in 'sales' always matches an existing 'id' in 'item_master'.

upvoted 0 times

...

Tegan

12 months ago

That makes sense, we want to reference the 'id' column in 'item_master' from the 'item_id' column in 'sales'.

upvoted 0 times

...

Trevor

12 months ago

I think the correct SQL statement is B) FOREIGN KEY (item_id) REFERENCES item_master (id)

upvoted 0 times

...

Tonette

1 year ago

I'll go with D. It just makes sense to reference the primary key of the 'item_master' table, right? That way, we can ensure the integrity of the data.

upvoted 0 times

...

Sharee

1 year ago

Hmm, I'm not sure. It's been a while since I've dealt with SQL constraints. Maybe I should brush up on my database fundamentals before taking this exam.

upvoted 0 times

Lucina

11 months ago

User1: Definitely, it helps maintain data integrity in the database.

upvoted 0 times

...

Vivienne

12 months ago

User2: That makes sense. It's important to have the foreign key reference the primary key in the other table.

upvoted 0 times

...

Rory

1 year ago

User1: I think the correct SQL statement is B) FOREIGN KEY (item_id) REFERENCES item_master (id)

upvoted 0 times

...

Dallas

1 year ago

I think the answer is B. The 'item_id' in the 'sales' table should reference the 'id' column in the 'item_master' table, not the other way around.

upvoted 0 times

Chau

1 year ago

Thanks for confirming! It's important to set up the foreign key relationship correctly for data integrity.

upvoted 0 times

...

Lashaunda

1 year ago

That's correct! The foreign key in the 'sales' table should reference the 'id' column in the 'item_master' table.

upvoted 0 times

...

Felix

1 year ago

B) FOREIGN KEY (item_id) REFERENCES item_master (id)

upvoted 0 times

...

Justine

1 year ago

I'm not sure about the answer. Can someone explain why option B is the correct one?

upvoted 0 times

...

Evangelina

1 year ago

I agree with Earlean. Using FOREIGN KEY (item_id) REFERENCES item_master (id) ensures that the 'item_id' in 'sales' table will always have a value that exists in the 'id' column of 'item_master' table.

upvoted 0 times

...

Earlean

1 year ago

I think the correct answer is B) FOREIGN KEY (item_id) REFERENCES item_master (id). It makes sense to reference the 'id' column in 'item_master' table.

upvoted 0 times

...