| Author |
 Topic  |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
 Posted - 01/16/2013 : 03:33:15
|
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
2242 Posts |
Posted - 01/16/2013 : 04:00:30
|
I am not getting your point. can you post the sample data of first and second tables which you want to insert (clearly)?
--
Chandu |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 01/16/2013 : 05:22:26
|
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 |
|
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 01/16/2013 : 05:31:20
|
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? |
|
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 01/16/2013 : 05:32:04
|
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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 01/16/2013 : 06:14:08
|
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 |
|
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 01/16/2013 : 08:02:26
|
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! |
|
|
| |
Topic |
|
|
|
Handling foreign key insert / delete