CODEWITHSUNDEEP
mysqlsqldatabase
Carry
Carry

Reputation: 11

Can a foreign key reference the primary key in the same table?

I'm creating a comment system under a post article. I have one table for comments with ID, post_id, user_id, text and comment_id column. So my thinking is, that when I add a comment under some post, the comment_id will be set to null, but if I comment another comment, the comment_id will be set to ID of the comment ID I'm commenting, which leads me to my question: is it possible to have foreign key that references the ID in the same table or is there a better way to solve this?

Upvotes: 1

Views: 426

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Can a foreign key reference the primary key in the same table?

Yes, it is possible:

CREATE TABLE comments(
  id INT PRIMARY KEY,
  comment_id INT REFERENCES comments(id)
  -- other columns
)

db<>fiddle demo

Upvotes: 1

Related Questions