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  |
|
|
|