PostgreSQL Exam PGCES-02 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

Millie

1 months 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 months 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

Kanisha

6 days ago

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

upvoted 0 times

...

Josphine

12 days ago

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

upvoted 0 times

...

Darell

2 months 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 days ago

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

upvoted 0 times

...

Tegan

26 days 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

29 days ago

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

upvoted 0 times

...

Tonette

2 months 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

2 months 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 days ago

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

upvoted 0 times

...

Vivienne

24 days 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

2 months ago

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

upvoted 0 times

...

Dallas

2 months 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

2 months ago

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

upvoted 0 times

...

Lashaunda

2 months 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

2 months ago

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

upvoted 0 times

...

Justine

3 months ago

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

upvoted 0 times

...

Evangelina

3 months 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

3 months 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

...