Self-referenced and Check Constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Self-referenced and Check Constraint

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-09-11 : 18:45:18

Hi all,
First I am sorry for ugly writing because my English is limited absolutely.
Consider following family tree:


		David
               /  |   \  
 	      /   |    \		     
	    John Lisa Sarah
             |           
             |			
           James

I implement it into a table like this:

--Design
create table family_tree
(
	node uniqeidentifier primary key,
	name nvharchar(25)
	sex bit, --when 1 then Male else Female
	parent uniqueidentifier null      
)
--Date
insert into family_tree
select newid(), 'David', 1, null

I’ve created a self-referenced to this table for check parent of a new row (node) be in node list, like this:

alter table Family_Table with check add constraint [FK]
foreign key ([parent]) refrences Family_Table([node])

But parent of a new row (node) cannot be a FEMALE node!
To this reason I have created a very simple instead of insert trigger to achieve it like this:

create trigger trg_I_I
on family_tree
instead of insert, update
as
if exists(select * from tree where node=(select parent from inserted) and sex='M')
--OR
-->if exists(select * from inserted where parent in (select node from tree and sex='M')
insert...

But are some major differences between my trigger and check constraint
(For instance suppose we want to insert a group of rows; in check when a row is not inserted then fail…)
I want a FK that only use a sub-set of PK data where have sex=Male;

set of node--> {(David, M),(John, M),(Lisa, F),(Sarah,F)}
sub-set of node that sex=M --> {(David, M),(John, M)}

please...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 13:30:50

just add another check constraint which is based on udf below:-


CREATE FUNCTION GetNodeGender
(
@Node uniqueidentifier 
)
RETURNS bit 
AS
BEGIN
DECLARE @Sex bit
SELECT @Sex=Sex
FROM family_tree
WHERE node=@Node
RETURN @Sex
END



ALTER TABLE familyfamily_tree ADD CONSTRAINT CHK_Parent_Gender CHECK ( dbo.GetNodeGender(Parent)= 1)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-09-14 : 09:44:00

or:


ALTER TABLE family_tree
ADD ParentSex bit NULL
CONSTRAINT DF_family_tree_ParentSex DEFAULT(1)
CONSTRAINT CK_family_tree_ParentSex CHECK(ParentSex = 1)

ALTER TABLE family_tree
ADD CONSTRAINT UQ_family_tree_node_sex
UNIQUE(node, sex)

ALTER TABLE family_tree
ADD CONSTRAINT FK_family_tree_parent_ParentSex
FOREIGN KEY (parent, ParentSex)
REFERENCES family_tree(node, sex)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-09-15 : 04:50:36

Thank you so much!

--> For visakh16
I knew at CHECK() constraint we cannot use subquery, but I did not know we can use UDF at it.
It is interesting! (At least for me)

--> For Ifor
The composite foreign key constraint was (or is) a new thing for me.
Little, little I am growing up.

--> For all
Joe CELKO introduce a method that called Nested Set, But I am not familiar with SQL so much to this reason I use Adjacency List Model.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources