CODEWITHSUNDEEP
sql-server
Justin Adkins
Justin Adkins

Reputation: 1234

Reference a different column in that same table

Is it possible to have a column in a table reference a different column in that same table? Here is my situation:

I'm trying to create a database in my table to store comments. Some of my columns are CommentID, UserID, ParentID, Content.

CommentID is the IDENTITY PRIMARY KEY and UserID references the User table. ParentID basically will form the hierarchy for the comments. It should reference CommentID. Is there a way to reference CommentID or to ensure that the value for ParentID equals a record where that is matched?

Data

Comment ID     ParentID     Content
-----------------------------------------
1              NULL         Love It!
2              1            That's a stupid comment.
3              2            No, you left the stupid comment.
4              1            Wow, much thanks!
5              4            Seriously?

I also have a timestamp column which will further sort the comments. I'm not even sure if I need to add any special key, I just don't want to run into trouble down the road. Thanks!

Upvotes: 0

Views: 1156

Answers (1)

Mike Dinescu
Mike Dinescu

Reputation: 55760

Yes, you can have a column in a table reference another in the same table. In your case creating a foreign key constraint from ParentID to CommentID would be perfectly OK.

CREATE TABLE Comments (
   CommentID INT PRIMARY KEY,
   ParentID INT,
   Comment VARCHAR(MAX),
   FOREIGN KEY (ParentID) REFERENCES Comments(CommentID)
)

INSERT INTO Comments (CommentID, ParentID, Comment) VALUES (1, NULL, 'First Comment')

INSERT INTO Comments (CommentID, ParentID, Comment) VALUES (2, NULL, 'Second Comment')

INSERT INTO Comments (CommentID, ParentID, Comment) VALUES (3, 1, 'Sub Comment')

INSERT INTO Comments (CommentID, ParentID, Comment) VALUES (4, 3, 'Sub Sub Comment')

-- The following will fail with constraint violation
INSERT INTO Comments (CommentID, ParentID, Comment) VALUES (5, 10, 'Invalid Sub Comment 2')

Upvotes: 2

Related Questions