SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Handling foreign key insert / delete
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 01/16/2013 :  03:33:15  Show Profile  Reply with Quote
When I insert / delete data to two tables, (how) can I keep the links correct automatically?

I have two tables, Measurements and Comments. For each measurement there is 0-n comments. I have the Measurements table like this:
ID Result
where the ID is an autonumber and result is whatever.
The Comments table is like this:
ID MeasID Comment
where the ID is an autonumber and Comment is a string, but that MeasID makes the problem.

Now when I save a result like this
Result: 8
Comment: Looks like we got an 8 here.
Comment: Doesn't seem too serious.
Comment: Keep on, that's ok.

the Measurements table gets one row, like:
0 8
the Comments table gets three rows
0 0 Looks like we got an 8 here.
1 0 Doesn't seem too serious.
2 0 Keep on, that's ok.

How do I get that 0 (MeasID) to the comments table? Is the only way to query the Measurements table for the max ID, add one to that and use that as MeasID?

Same then with deleting, do I need to delete the orphan records with some
WHERE MeasID = IDtobedeleted?

Or does SQL Server 2008 have some feature that does all this foreign key handling ("linking") for me automatically?

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 01/16/2013 :  04:00:30  Show Profile  Reply with Quote
I am not getting your point. can you post the sample data of first and second tables which you want to insert (clearly)?

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 01/16/2013 :  05:22:26  Show Profile  Reply with Quote

See this example by using Primary key and Foreign key..........

CREATE TABLE Measurements
 (ID int identity(0,1) PRIMARY KEY, 
  Result varchar(10)
 )

CREATE TABLE Comments 
(ID int identity(0,1) Primary key,
 MeasID int references Measurements(ID), 
 Comment varchar(20)
)

insert into Measurements values('8')

SELECT * FROM Measurements

INSERT INTO Comments (MeasID, Comment)
SELECT MAX(id), 'comment1' 
FROM Measurements

SELECT * FROM Comments


--
Chandu
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 01/16/2013 :  05:31:20  Show Profile  Reply with Quote
Table 1 (Measurements):
ID Result
0 2
1 8
2 70
3 something
4 something else
5 other value

Table 2 (Comments)
ID MeasID Comment
0 1 Something
1 1 Something else
2 1 yet another value
3 2 what is this comment
4 4 comment here

where that MeasID is foreign key to Measurements table.

In other words, this could be made like this:
Combined Table:
ID Result Comment
0 2 NULL
1 8 Something
2 8 Something else
3 8 yet another value
4 70 what is this comment
5 something NULL
6 something else comment here
7 other value NULL

but clearly it shouldn't be implemented this way, because, after all, we are talking about a relational database.

So now the problem is that when I add a measurement value, while the first table (Measurements) behaves correctly so that there comes a new row like
ID Result
6 MyNewMeasurementResult

I have no idea where the second table is supposed to draw that MeasID column from. Sure, ID is 5 and comment is whatever, but how can I assign that autonumber value of 6 from Measurements to there? Is the only way to query for the max ID of Measurements table, add 1 to that and insert the row using that +1 (5+1=6) as MeasID?
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 01/16/2013 :  05:32:04  Show Profile  Reply with Quote
quote:
Originally posted by bandi


See this example by using Primary key and Foreign key..........

CREATE TABLE Measurements
 (ID int identity(0,1) PRIMARY KEY, 
  Result varchar(10)
 )

CREATE TABLE Comments 
(ID int identity(0,1) Primary key,
 MeasID int references Measurements(ID), 
 Comment varchar(20)
)

insert into Measurements values('8')

SELECT * FROM Measurements

INSERT INTO Comments (MeasID, Comment)
SELECT MAX(id), 'comment1' 
FROM Measurements

SELECT * FROM Comments


--
Chandu



Yeah, this looks good!

edit. It just seems like this is mostly used for updating and deleting the data i.e. it still doesn't tell me that when I insert the record with multiple comments, that the comments would know that they are supposed to get the MeasID from the newly created autonumber of their measurement "counterpart".

Edited by - KilpAr on 01/16/2013 05:38:06
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 01/16/2013 :  06:14:08  Show Profile  Reply with Quote
see this example.. You can use SCOPE_IDENTITY property to know the last isentity value of the corresponding column
create table testId(id int identity (0,1), name varchar(2))
insert into testId VALUES('ch'),('df'), ('aw'),('sd')
SELECT SCOPE_IDENTITY() --3
SELECT * FROM testID

--
Chandu
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 01/16/2013 :  08:02:26  Show Profile  Reply with Quote
quote:
Originally posted by bandi

see this example.. You can use SCOPE_IDENTITY property to know the last isentity value of the corresponding column
create table testId(id int identity (0,1), name varchar(2))
insert into testId VALUES('ch'),('df'), ('aw'),('sd')
SELECT SCOPE_IDENTITY() --3
SELECT * FROM testID

--
Chandu



Ok, I think this solves it. The key thing I was missing was the certainty that I connect the right comments to the right measurements - if two happens at the very same second, this that you posted should have no problems with it. Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000