constraint on RowParentId within same table ?

Question

How do I specify constraint on my field that allows null but if value exists it should be one of values of primary key within existing table? Take a look at the code:

  CREATE TABLE TestTable 
(
    RowId int IDENTITY NOT NULL PRIMARY KEY,
    RowParentId int NULL, -- < how do I specify constraint that RowParentId if not NULL should be RowId (foreign key to existing table?)
    RowName nvarchar(30),
    RowShortName nvarchar(10)
)
GO

I want to be able to generate parent child view without limiting depth and enforcing constraint on existing parent.

Hope I was able to convey what I'm looking for.

Cheers

Answer 1

Isn't that just a foreign key?

RowParentId int NULL references ParentTable (ParentTableIdColumn),

if it is not null, then it must be a value from the parent table.

Answer 2

ALTER TABLE TestTable
ADD CONSTRAINT fk_testtable_parent
FOREIGN KEY (RowParentId)
REFERENCES TestTable(RowId)

Note that keeping a NULL in the column is a bad idea, as it's not searcheable by indexes.

You better add a surrogate record with id = 0, reference your real root to this record, and exclude it from selects.

Answer 3

You can have a foreign key constraint that references back to the same table. You may want to look into other models for hierarchies though. The linked chain model has a lot of problems in SQL. Do a Google on "Joe Celko hierarchies" and you should be able to find information on other ways to model a hierarchy.

Answer 4

You want to create a foreign key. There are several ways to do this, but the simplest way for a single key like this would be to add the following after the workld "null" for that column:

references table_name (column_name)

You may need to add the words "foreign key" before that, I can't remember. Either way that should take care of it.

Answer 5

ALTER TABLE [dbo].TestTable WITH CHECK ADD CONSTRAINT [FK_TestTable_RowId_TestTable_RowParentId] FOREIGN KEY(RowParentId) REFERENCES TestTable(RowId)