Geek Answers Handbook

Bad practice to create a foreign key constraint on the primary key of the same table?

I am trying to make a comment table in my database and store the comments in the same table. I would put a foreign key constraint on a column named "ParentId" that would be associated with the CommentId column, which is the primary key of the same table. If this is a parent comment, I would give it null for ParentId. Is this a bad practice? If so, which methods are best suited to solve this problem.

+4
source share
3 answers

This is normal. You need to think about what to do if a comment with children is deleted. The FK restriction will prevent deletion, and the general solution to cascade deletion is not suitable here, as well as the NULL output of the parent link. Therefore, you may have to leave a deleted comment and simply clear the content or otherwise mark it as deleted.

+4
source

this is not a bad practice, but as commentators note, you may run into problems later.

Since commentary on comments boils down to a logical tree, it might be worth checking out Celko:

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

VERY COMFORTABLE!

+2
source

http://msdn.microsoft.com/en-us/library/ms124432(v=sql.100).aspx

This is not a bad practice for SQL Server, see the link above for the Definaion of the Employee table and refer to the ManagerID column, Microsoft uses this script in the sample DB that comes with the SQL server.

The main thing to take care of is that the parent record cannot be physically deleted; you may need to use soft delete using the flag.

+1
source

More articles:


All Articles