Constraint Question when referencing a Parent-child structured table

I have a Location Table, which has a ring constraint, so that I can map out
a parent-Child relationship. I use this table to create a treeview in my
application.
==========
CREATE TABLE [J_Location] (
[LocationID] [int] NOT NULL ,
[LocationName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[LocationparentID] [int] NULL ,
CONSTRAINT [J_Location_PK] PRIMARY KEY CLUSTERED
(
[LocationID]
) ON [PRIMARY] ,
CONSTRAINT [J_Location_J_Location_FK1] FOREIGN KEY
(
[LocationparentID]
) REFERENCES [J_Location] (
[LocationID]
),
CONSTRAINT [J_Location_ring] CHECK ([LocationparentID] <> [LocationID])
) ON [PRIMARY]
GO
==========
I also have another table that references my location table
==========
CREATE TABLE [J_Incident] (
[IncidentID] [int] IDENTITY (1, 1) NOT NULL ,
[LocationID] [int] NOT NULL ,
[WhatHappened] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [J_Incident_PK] PRIMARY KEY CLUSTERED
(
[IncidentID]
) ON [PRIMARY] ,
CONSTRAINT [J_Location_J_Incident_FK1] FOREIGN KEY
(
[LocationID]
) REFERENCES [J_Location] (
[LocationID]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
==========
I want to put a contraint on this second table so that it can not have an
entry in the LocationID if the Location record has children. To say that
another way only location rows that have no children are valid records.
To give you an example.
Here is the Location Records
==========
INSERT INTO J_Location([LocationID], [LocationName])
VALUES (0, 'World')
INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
VALUES (1, 'Country1', 0)
INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
VALUES (2, 'Country2', 0)
INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
VALUES (3, 'City1', 1)
INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
VALUES (4, 'City2', 1)
INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
VALUES (5, 'City3', 2)
INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
VALUES (6, 'City4', 2)
GO
==========
When I now make an entry in my incident table, only locationID's 3, 4, 5, 6
are valid - 0, 1, 2 are invalid because they have children.
So entries in my Incident table like so
==========
INSERT INTO J_Incident([LocationID], [WhatHappened])
VALUES (2, 'Some Text')
INSERT INTO J_Incident([LocationID], [WhatHappened])
VALUES (4, 'Some Text')
INSERT INTO J_Incident([LocationID], [WhatHappened])
VALUES (6, 'Some Text')
GO
==========
In the above inserts I would expect the 1st entry to fail and it references
a location code that has children.
The question is: How do I impliment this constaint?
__AllanYou could call a function within the check constraint
ALTER Table CheckConstraint
ADD Constraint CHECK_Existence CHECK
(
Location <> 0
)
--OR
If its a bit more complex than that, checking for existence in another table
call a functon within a check constraint:
ALTER Table CheckConstraint
ADD Constraint CHECK_Existence CHECK
(
dbo.CheckthisVae(SomeValue) --Return either 1 (true) or 0 (false)
)
or create a trigger which checks the value and refuses it if it is not valid
for inserting.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Allan Wissing" <Allan.Wissing@.ComputerCraft.co.nz> schrieb im Newsbeitrag
news:d5ojla$r6m$1@.lust.ihug.co.nz...
>I have a Location Table, which has a ring constraint, so that I can map out
>a parent-Child relationship. I use this table to create a treeview in my
>application.
> ==========
> CREATE TABLE [J_Location] (
> [LocationID] [int] NOT NULL ,
> [LocationName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [LocationparentID] [int] NULL ,
> CONSTRAINT [J_Location_PK] PRIMARY KEY CLUSTERED
> (
> [LocationID]
> ) ON [PRIMARY] ,
> CONSTRAINT [J_Location_J_Location_FK1] FOREIGN KEY
> (
> [LocationparentID]
> ) REFERENCES [J_Location] (
> [LocationID]
> ),
> CONSTRAINT [J_Location_ring] CHECK ([LocationparentID] <> [LocationID])
> ) ON [PRIMARY]
> GO
> ==========
> I also have another table that references my location table
> ==========
> CREATE TABLE [J_Incident] (
> [IncidentID] [int] IDENTITY (1, 1) NOT NULL ,
> [LocationID] [int] NOT NULL ,
> [WhatHappened] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> CONSTRAINT [J_Incident_PK] PRIMARY KEY CLUSTERED
> (
> [IncidentID]
> ) ON [PRIMARY] ,
> CONSTRAINT [J_Location_J_Incident_FK1] FOREIGN KEY
> (
> [LocationID]
> ) REFERENCES [J_Location] (
> [LocationID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ==========
> I want to put a contraint on this second table so that it can not have an
> entry in the LocationID if the Location record has children. To say that
> another way only location rows that have no children are valid records.
> To give you an example.
> Here is the Location Records
> ==========
> INSERT INTO J_Location([LocationID], [LocationName])
> VALUES (0, 'World')
>
> INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
> VALUES (1, 'Country1', 0)
>
> INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
> VALUES (2, 'Country2', 0)
>
> INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
> VALUES (3, 'City1', 1)
> INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
> VALUES (4, 'City2', 1)
> INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
> VALUES (5, 'City3', 2)
> INSERT INTO J_Location([LocationID], [LocationName], [LocationparentID])
> VALUES (6, 'City4', 2)
> GO
> ==========
> When I now make an entry in my incident table, only locationID's 3, 4, 5,
> 6 are valid - 0, 1, 2 are invalid because they have children.
> So entries in my Incident table like so
> ==========
> INSERT INTO J_Incident([LocationID], [WhatHappened])
> VALUES (2, 'Some Text')
> INSERT INTO J_Incident([LocationID], [WhatHappened])
> VALUES (4, 'Some Text')
> INSERT INTO J_Incident([LocationID], [WhatHappened])
> VALUES (6, 'Some Text')
> GO
> ==========
> In the above inserts I would expect the 1st entry to fail and it
> references a location code that has children.
> The question is: How do I impliment this constaint?
> __Allan
>|||Jens,
Thank-you for your supply. I spent some time laying out the tables because I
wanted a more precise answer - would you use a contraint that calls a
function or a trigger - which is the correct way?
I also would mind some help on the Syntax <g>?
__Allan|||1) Get a copy of TREES & HIERARCHIES IN SQL.
2) Use a nested sets model.
CREATE TABLE Locations
(location_id INTEGER NOT NULL PRIMARY KEY.
location_name VARCHAR (30) NOT NULL,
lft INTEGER NOT NULL CHECK(lft > 0) UNIQUE,
rgt INTEGER NOT NULL CHECK(rgt > lft) UNIQUE);
CREATE TABLE Incidents
(incident_id INTEGER NOT NULL,
location_id INTEGER NOT NULL
REFERENCES Locations location_id)
ON UPDATE CASCADE,
incident_descrp VARCHAR(255) NOT NULL);
have an entry in the location_id if the Location record [sic] has
children [sic]. To say that another way only location rows that have no
children [sic] are valid records [sic]. <<
You might want to learn the differences between a row and record and
between a referenced table and a parent/child linkage. You are still
speaking in 1970's pre-relational terms.
You can do this in SQL-99 with a constraint on Incidents:
CHECK
(EXISTS
(SELECT *
FROM Locations AS L1
WHERE L1.location_id = Incidents.location_id
AND L1.lft = L1.rgt -1));
But in SQL Server, you will have to put this in a TRIGGER.|||Another thing I am not sure of is "Do I overwrite the existing Foreign Key
constraint as the is really now of no use and can I overwrite it?
__Allan|||Are you able to change the database structure now, or will you be stuck in
that ? I would put these things in separate entities. But thats another
thing... (just tell if you are able to). Also I would put a NULL instead of
0 in the table and eliminate the World entry. (As far as you are missing
some top level values like Earth -- Milkyway - Universe ... ;-) )
Based on your structure now that would be the follwing syntax (indicating
that you put in the NULL Value)
ALTER Table J_Location
ADD Constraint CHECK_Existence CHECK
(
dbo.CheckthisValue(LocationID)
)
Create function dbo.CheckthisValue
(
@.LocationID Int
)
Returns int
AS
BEGIN
IF EXISTS(Select * from J_Location Where LocationparentID =
@.LocationparentID)
Return 0
ELSE
RETURN 0
END
END
But as I am writing this statement I have to realize that, that you wouldnt
alo be allowed to insert a ParentID, becaue it alsways exsists in the
database if a child is created. By setting a self reference on the table
referencing child to parent ID you can make sure that nothing is entered
that already got such an parentID.
See how this works or if you wanna try it with a trigger (should be
perferable in that case a function)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Allan Wissing" <Allan.Wissing@.ComputerCraft.co.nz> schrieb im Newsbeitrag
news:d5oojc$571$1@.lust.ihug.co.nz...
> Jens,
> Thank-you for your supply. I spent some time laying out the tables because
> I wanted a more precise answer - would you use a contraint that calls a
> function or a trigger - which is the correct way?
> I also would mind some help on the Syntax <g>?
> __Allan
>|||What FK do yo mean ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Allan Wissing" <Allan.Wissing@.ComputerCraft.co.nz> schrieb im Newsbeitrag
news:d5ooq4$58h$1@.lust.ihug.co.nz...
> Another thing I am not sure of is "Do I overwrite the existing Foreign Key
> constraint as the is really now of no use and can I overwrite it?
> __Allan
>|||Jens,
> Are you able to change the database structure now
Yes this is a new design so I am still at the drawing board stage. I did
not pick up on what you did not like about my structure?
The Constaint Check and the Function worked fine - Thank-you for that!
__Allan|||Well on the second table we had
===========
[LocationID]
) REFERENCES [J_Location] (
[LocationID]
)
===========
And I was thinking now that we have designed a new Constraint on this same
table is the first one superfullous?
__Allan|||>> You are still speaking in 1970's pre-relational terms
OK - You caught my out... But you still new what I meant <g>
Found your book on the net!
The scripts you supplied, recievied an error with MS SQL
-Column CHECK constraint for column 'rgt' references another column, table
'Locations'.
So Joe, are you saying I'm doing this wrong? All I'm trying to do is
construct a treeview, and the location table is a lookup.
Having also implemented Jen's Contraint, I believe I'm getting the desired
result... Your thoughts please?
__Allan