increment child records based on parent id

Question

increment child records based on parent id

jvenezia

SSC Enthusiast

Points: 158
More actions
June 19, 2009 at 5:23 am

#207174

I have a parent table as follows:
*family_ID int identity(1,1)
familyName varchar(50)
with child table:
*family_ID int
*person_id int (identity(1,1)
person_name varchar(100)
with another child table (grandchild?):
*family_id int
*person_id int
*gc_id int identity(1,1)
gc_name varchar(100)
* denotes primary key fields
now, I need to change the person_id values based on someone else's values which can't be don without removing the identity col. I can move those records out to a temp table and delete and reinsert with IDENTITY_INSERT person ON. I risk not getting all records changed and then possibly having duplicates.
Now the question is: Is there a way to increment person_id over the family Id without using identity? Or, is there a way to remove the identity, update it and put it back while maintaining referential integrity with teh rest of the family?
If I add a record to the person table, how do I increment for just the family_id record it relates to? Person_id only has to be unique for a give family_ID.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

your'e trying to use an identity() column in the wrong way; an identity should simply identify one row as unique, and should never repeat...what you want is basically a ranking issue, were people added to the family are #1 and 2 for family one, and repeating 1 & 2 for family 2

there;'s no need to actually store that value int he table...i would use a view instead.

the ranking value you want can easily be done using the row_number function.

here's an example based on a cleaned up vesion of your tables:

Create Table Family (

family_ID int identity(1,1) PRIMARY KEY,

familyName varchar(50) )

INSERT INTO Family(familyName) VALUES ('Rubble') --1

INSERT INTO Family(familyName) VALUES ('Flintstone') --2

CREATE TABLE Persons(

person_id int identity(1,1) PRIMARY KEY,

family_id int REFERENCES Family(family_id),

person_name varchar(100))

INSERT INTO Persons(family_id,person_name) VALUES(1,'Barney')

INSERT INTO Persons(family_id,person_name) VALUES(1,'Betty')

INSERT INTO Persons(family_id,person_name) VALUES(2,'Fred')

INSERT INTO Persons(family_id,person_name) VALUES(2,'Wilma')

Create Table GrandChildren (

gc_id int identity(1,1) PRIMARY KEY,

family_id int REFERENCES Family(family_id),

person_id int REFERENCES Persons(person_id),

gc_name varchar(100) )

insert into GrandChildren(family_id,person_id,gc_name) values(1,1,'Bam Bam')

insert into GrandChildren(family_id,person_id,gc_name) values(2,3,'Pebbles')

--now use the row_number to get the desired "order", which is independant of the identity:

select

Family.family_id,

GrandChildren.gc_name,

Persons.person_name,

Family.familyName,

row_number() over(partition by Family.family_id order by Family.family_id,Persons.person_id) As FamilyRank

from Family

left outer join Persons on Family.family_id = Persons.family_id

left outer join GrandChildren on Persons.person_id = GrandChildren.person_id

--results

family_id gc_name person_name familyName FamilyRank

1 Bam Bam Barney Rubble 1

1 NULL Betty Rubble 2

2 Pebbles Fred Flintstone 1

2 NULL Wilma Flintstone 2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jvenezia SSC Enthusiast Points: 158 More actions · Answer 2

Ok - but the problem is, sometimes the parent_id could change from another system. That's where I'm getting lost here. I'm trying to avoid using the parent_id as an identity. What uniquely identifies the parent row is family_id and parent_id, but parent_id may be given to me from another system, so I can't really use identity. I could end up with 2 parents in different families with the same parent_id. So would it make sense to use a UDF to get default value like MAX(parent_id) +1 ? would performance suffer?

Thanks Lowell for the reply- Sorry if I have made a mistake with this or I am missing something. This is my first post on a forum.

Lowell (6/19/2009)
your'e trying to use an identity() column in the wrong way; an identity should simply identify one row as unique, and should never repeat...what you want is basically a ranking issue, were people added to the family are #1 and 2 for family one, and repeating 1 & 2 for family 2
there;'s no need to actually store that value int he table...i would use a view instead.
the ranking value you want can easily be done using the row_number function.
here's an example based on a cleaned up vesion of your tables:
Create Table Family (
family_ID int identity(1,1) PRIMARY KEY,
familyName varchar(50) )
INSERT INTO Family(familyName) VALUES ('Rubble') --1
INSERT INTO Family(familyName) VALUES ('Flintstone') --2
CREATE TABLE Persons(
person_id int identity(1,1) PRIMARY KEY,
family_id int REFERENCES Family(family_id),
person_name varchar(100))
INSERT INTO Persons(family_id,person_name) VALUES(1,'Barney')
INSERT INTO Persons(family_id,person_name) VALUES(1,'Betty')
INSERT INTO Persons(family_id,person_name) VALUES(2,'Fred')
INSERT INTO Persons(family_id,person_name) VALUES(2,'Wilma')
Create Table GrandChildren (
gc_id int identity(1,1) PRIMARY KEY,
family_id int REFERENCES Family(family_id),
person_id int REFERENCES Persons(person_id),
gc_name varchar(100) )
insert into GrandChildren(family_id,person_id,gc_name) values(1,1,'Bam Bam')
insert into GrandChildren(family_id,person_id,gc_name) values(2,3,'Pebbles')
--now use the row_number to get the desired "order", which is independant of the identity:
select
Family.family_id,
GrandChildren.gc_name,
Persons.person_name,
Family.familyName,
row_number() over(partition by Family.family_id order by Family.family_id,Persons.person_id) As FamilyRank
from Family
left outer join Persons on Family.family_id = Persons.family_id
left outer join GrandChildren on Persons.person_id = GrandChildren.person_id
--results
family_id gc_name person_name familyName FamilyRank
1 Bam Bam Barney Rubble 1
1 NULL Betty Rubble 2
2 Pebbles Fred Flintstone 1
2 NULL Wilma Flintstone 2

Lowell SSC Guru Points: 323518 More actions · Answer 3

Ok - but the problem is, sometimes the parent_id could change from another system. That's where I'm getting lost here. I'm trying to avoid using the parent_id as an identity. What uniquely identifies the parent row is family_id and parent_id, but parent_id may be given to me from another system, so I can't really use identity. I could end up with 2 parents in different families with the same parent_id. So would it make sense to use a UDF to get default value like MAX(parent_id) +1 ? would performance suffer?
Thanks Lowell for the reply- Sorry if I have made a mistake with this or I am missing something. This is my first post on a forum.

ok, correct me if i'm wrong, I had a few assumptions that are probably wrongwhen i read your post.

you have a table with family_id and parent_id, but when you migrate data into it from another system, one or more of the id's might be duplicates of existing data, right?

in that case, have a completely separate identity column, and migrate data into it as needed, maybe even bringing in a 3rd column so i know the original source:

Create Table Whatever(

WhateverID int identity(1,1) not null primary key,

OriginalDataSource varchar(100) default 'ThisServer',

family_id int,

parent_id int,

constraint UQ_Source_Parent_Family unique (OriginalDataSource ,family_id,parent_id)

...

)

that way when i insert data from Database1, i insert the two columns, never need to "change" them to due to my identity fields, and can always refer back to where i got them in the fdirst palce in case i need to join/compare.

am I on the right track to understanding your requirement?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jvenezia SSC Enthusiast Points: 158 More actions · Answer 4

Thanks again Lowell - By the way my name is Joe.

I had considered carrying both, but carrying 2 id's seems excessive because it will be blank for 75% of the families. Also, I will be updating information from the other systems. It wouldn't matter because I could update via the other field. And I could create another intermediary table for families from other systems with family_id, my_parent_id and their_parent_id so I'm only storing the data for those that come from teh other sytem. I guess what I'm really trying to do is stick to 1 ID (Their's if it exists or some unique value until I interface with the other system) I'd also like to be able to change the parent_Id since there are alot of deletes and it would be nice to recapture some holes in the sequence. Potentially I could need a bigit for parent_id unless I can use the real world unique identifier that includes the family_id.