Handling foreign key insert / delete

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

Transact-SQL (2008)

Handling foreign key insert / delete

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2013-01-16 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 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 - 2013-01-16 : 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 - 2013-01-16 : 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".

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 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 - 2013-01-16 : 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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources