Monday, February 20, 2012

Is my self-referencing table well built ?

here is my CREATE TABLE statement:
/* Sections Table */
CREATE TABLE PsychoCMS.dbo.Sections (
ID Int NOT NULL IDENTITY(0,1)
CONSTRAINT PK_Section_ID PRIMARY KEY,

Father Int NULL
CONSTRAINT FK_Section_Father
FOREIGN KEY REFERENCES PsychoCMS.dbo.Sections(ID),

Title Nvarchar(50) NOT NULL,

InitialDoc Int NULL
CONSTRAINT FK_Section_InitialDoc
FOREIGN KEY REFERENCES PsychoCMS.dbo.Documents(ID)
)

ALTER TABLE PsychoCMS.dbo.Sections
ADD CONSTRAINT DF_Section_Father
DEFAULT 0 FOR Father

Since each section can be a child of another section the Father field reference the ID field.
I think everything fine, but I'm not sure about the root sections. I made the Father field nullable so if the field is empty, it mean it's a root section, but is this the good way to go ? Will I get error from foreign key when I try to add a root section ?Looks fine to me.|||Thanks a lot.

No comments:

Post a Comment