March 24, 2009 at 9:01 am
Hi.
I have a situation of a hierarchy where I only want a parent and zero or more children but that's as deep as the hierarchy goes (the children never have children). Is there some easy way to control this with some sort of constraint?
Here's my table def. so far (assistance from GSquared - Thanks man!)
IF OBJECT_ID(N'dbo.Volumetric_Estimate',N'U') IS NOT NULL DROP TABLE dbo.Volumetric_Estimate
CREATE TABLE dbo.Volumetric_Estimate (
Estimate_DBKey INT IDENTITY NOT NULL
,Parent_DBKey INT NULL
,Deposit_DBKey INT NOT NULL
,Type VARCHAR(5) NOT NULL default('Whole')
,CONSTRAINT PK_Volumetric_Estimate PRIMARY KEY CLUSTERED (Estimate_DBKey)
,CONSTRAINT FK_Estimate_To_Deposit FOREIGN KEY (Deposit_DBKey) REFERENCES dbo.Coal_Deposit (Deposit_DBKey)
,CONSTRAINT FK_Parent_VE FOREIGN KEY (Parent_DBKey) REFERENCES dbo.Volumetric_Estimate (Estimate_DBKey)
,CONSTRAINT CK_Parent CHECK (Estimate_DBKey > Parent_DBKey OR Parent_DBKey IS NULL)
,CONSTRAINT CK_Type CHECK (TYPE IN ('Whole','Part'))
,CONSTRAINT CK_TypeParent CHECK (Type='Whole' and Parent_DBKey IS NULL OR Type='Part' AND Parent_DBKey IS NOT NULL));
March 24, 2009 at 9:42 am
I tried creating a test table with a constraint that looks like it should restrict the hierarchy to one level, but I must be doing something wrong.
Here's the test table:
if object_id(N'OneLevelHierarchy','U') is not null
drop table dbo.OneLevelHierarchy;
go
create table OneLevelHierarchy (
ID int identity primary key,
ParentID int null,
ParentOfParentID int null,
constraint CK_ParentOfParent check (ParentOfParentID is null));
go
create unique index UIX_OneLevelHierarchy_Parents on dbo.OneLevelHierarchy
(ID, ParentID);
go
alter table dbo.OneLevelHierarchy
add constraint FK_Parents foreign key (ParentID, ParentOfParentID) references dbo.OneLevelHierarchy(ID, ParentID)
insert into dbo.OneLevelHierarchy
default values;
insert into dbo.OneLevelHierarchy (ParentID)
select 1;
insert into dbo.OneLevelHierarchy (ParentID)
select 2;
select *
from dbo.OneLevelHierarchy;
Now, since there's an FK relating ParentID and ParentOfParentID to ID and ParentID, the attempt to insert a 2 into ParentID should fail, but it doesn't. That should FK to ID 2, which has ParentID 1, which means leaving ParentOfParentID null should violate the FK. But it successfully inserts, and creates a three-level hierarchy.
I think your best bet, in this case, is either a trigger that blocks inserting rows that have a ParentID that also has a ParentID, or do the same in the insert proc, if you have one.
If anyone can point out to me where my test table is mis-built, please do so.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 24, 2009 at 10:51 am
I think I've got it. I'm just not sure if this is the best solution.
I'm using a function called from a constraint which checks for grandparents.
What do you think? It seems to work. Is it a good solution from a technical standpoint?
BTW. All of the tables in my DB have the columns, Update_Date_Time and Updated_By_Id with defaults to set them and an update trigger to maintain them.
-----------------------------------------------------------------------------------------------
-- Coal_Volumetric_Estimate
-----------------------------------------------------------------------------------------------
USE Coal_Production_Test
IF OBJECT_ID(N'Check_For_VE_Grandparent',N'FN') IS NOT NULL DROP FUNCTION Check_For_VE_Grandparent
GO
CREATE FUNCTION dbo.Check_For_VE_Grandparent(@Parent_Id INT) RETURNS INT AS
BEGIN
DECLARE @RESULT AS INT
SET @RESULT = NULL
IF (SELECT Parent_Id FROM Coal_Volumetric_Estimate WHERE Volumetric_Estimate_Id = @Parent_Id) IS NOT NULL
SET @RESULT = 1
ELSE
SET @RESULT = 0
RETURN @RESULT
END
-----------------------------------------------------------------------------------------------
-- Coal_Volumetric_Estimate
-----------------------------------------------------------------------------------------------
USE Coal_Production_Test
IF OBJECT_ID(N'Coal_Volumetric_Estimate',N'U') IS NOT NULL DROP TABLE Coal_Volumetric_Estimate
CREATE TABLE Coal_Volumetric_Estimate (
Volumetric_Estimate_Id INT IDENTITY(1,1) NOT NULL
,Parent_Id INT NULL
,Coal_Bearing_Region_Name VARCHAR(30) NULL
,Coal_Field_Name VARCHAR(30) NULL
,Coal_Deposit_Name VARCHAR(30) NULL
,Coal_Permit_Volumetric_Estimate_Area_Name VARCHAR(30)
,Update_Date_Time DATETIME NOT NULL CONSTRAINT DF_CCoal_Volumetric_Estimate_Update_Date_Time DEFAULT GETDATE()
,Updated_By_Id INT NOT NULL CONSTRAINT DF_Coal_Volumetric_Estimate_Updated_By_Id DEFAULT dbo.Get_Staff_Id()
,CONSTRAINT PK_Coal_Volumetric_Estimate PRIMARY KEY CLUSTERED (Volumetric_Estimate_Id)
,CONSTRAINT FK_Child_Est_to_Parent_Est FOREIGN KEY (Parent_Id) REFERENCES Coal_Volumetric_Estimate (Volumetric_Estimate_Id)
,CONSTRAINT FK_Coal_Volumetric_Estimate_to_Coal_Deposit FOREIGN KEY (Coal_Bearing_Region_Name, Coal_Field_Name, Coal_Deposit_Name) REFERENCES Coal_Deposit (Coal_Bearing_Region_Name, Coal_Field_Name, Name) ON UPDATE CASCADE
,CONSTRAINT FK_Coal_Volumetric_Estimate_to_Coal_Permit_Volumetric_Estimate_Area FOREIGN KEY (Coal_Permit_Volumetric_Estimate_Area_Name) REFERENCES Coal_Permit_Volumetric_Estimate_Area (Name) ON UPDATE CASCADE
,CONSTRAINT CK_Deposit_or_Permit_Area_Estimate CHECK ((Coal_Bearing_Region_Name IS NULL AND Coal_Field_Name IS NULL AND Coal_Deposit_Name IS NULL AND Coal_Permit_Volumetric_Estimate_Area_Name IS NOT NULL) OR (Coal_Bearing_Region_Name IS NOT NULL AND Coal_Field_Name IS NOT NULL AND Coal_Deposit_Name IS NOT NULL AND Coal_Permit_Volumetric_Estimate_Area_Name IS NULL))
,CONSTRAINT CK_Grandchildren CHECK (dbo.Check_For_VE_Grandparent(Parent_Id) = 0)
)
GO
CREATE TRIGGER Coal_Volumetric_Estimate_Update ON Coal_Volumetric_Estimate FOR UPDATE AS
DECLARE @Staff_Id VARCHAR(5)
SET @Staff_Id = dbo.Get_Staff_Id()
UPDATE t1 SET
Update_Date_Time = GETDATE()
,Updated_By_Id = @Staff_Id
FROM Coal_Volumetric_Estimate t1
JOIN inserted i
ON i.Volumetric_Estimate_Id = t1.Volumetric_Estimate_Id
GO
INSERT INTO Coal_Volumetric_Estimate VALUES (
NULL
,'FOOTHILLS'
,'COALSPUR'
,'ANDERSON CREEK'
,NULL --'TEST'
,DEFAULT
,DEFAULT)
SELECT * FROM Coal_Volumetric_Estimate
INSERT INTO Coal_Volumetric_Estimate VALUES (
1
,'FOOTHILLS'
,'COALSPUR'
,'ANDERSON CREEK'
,NULL --'TEST'
,DEFAULT
,DEFAULT)
SELECT * FROM Coal_Volumetric_Estimate
INSERT INTO Coal_Volumetric_Estimate VALUES (
2
,'FOOTHILLS'
,'COALSPUR'
,'ANDERSON CREEK'
,NULL --'TEST'
,DEFAULT
,DEFAULT)
SELECT * FROM Coal_Volumetric_Estimate
March 24, 2009 at 2:48 pm
That should work.
The update audits are pretty standard. I don't use them, because any place I need to audit that, I need to know more than when it was changed and by whom. I need to know what was changed, from what and to what. But if when and who will do what you need, that should work okay.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply