CODEWITHSUNDEEP
sql-serverforeign-keysforeign-key-relationship
Edgar
Edgar

Reputation: 489

Insert data with foreign key SQL

I have two SQL tables ctgl_geometry and ctgl_vertices

ctgl_geometry will store the Geometry of how points are connected to each other, so it has an id, one value, the number of vertices and a foreign key to link the id to get a list of values of that geometry

CREATE TABLE ctgl_geometry (
  Id                    int primary key,
  otherVal              int,
  NoVertices            int,
  vertexID              int 
);
create table ctgl_vertices
(
  Id         int primary key,
  GeometryId int,
  val        int
);

for instance if there is a list of vertices {0,1,6,5,4} of length 5 I will store it vertices table like

INSERT INTO ctgl_vertices VALUES 
(1,101,0),
(2,101,1),
(3,101,6),
(4,101,5),
(5,101,4);

and in geometry like:

INSERT INTO ctgl_geometry VALUES 
(1, 10,5,101);

101 is the link to know the list.

But I do not know how to save the values if I do not know the identity value (101 in example)

So I was thinking on using IDENT_CURRENT to know the last inserted vertices table value like:

SELECT  IDENT_CURRENT('ctgl_vertices ') + IDENT_INCR('ctgl_vertices ');

and the result assign it to foreign key of ctgl_geometry vertexID

But I think there would be a better way of doing this...

How to insert a list of vertices and then assign the value to geometry?

I am also using this query to retrieve values:

select g.NoVertices,v.val from ctgl_geometry g 
      inner join ctgl_vertices v
on g.vertexID = v.GeometryId where g.otherVal = 10;

and get

NoVertices  val
5   0
5   1
5   6
5   5
5   4
4   1
4   2
4   7
4   6
4   2
4   3
4   8
4   7
4   4
4   5
4   10
4   9
4   5
4   6
4   11
4   10
4   6
4   7
4   12
4   11
4   7
4   8
4   13
4   12

Please take a look at fiddle

Upvotes: 0

Views: 1702

Answers (1)

M.Ali
M.Ali

Reputation: 69574

You Table schema needs some serious attention. You are logically defining Foreign Relationships in your head but not actually implementing them.

Your Referencing column must have a Foreign Key Constraint defined on it, In your case it is GeometryId.

And the column being referenced must be a Primary Key column which in your case will be vertexID

Taking these recommendations into consideration you Table Schema should be something like.... 

CREATE TABLE ctgl_geometry (
  Id                    int  ,
  otherVal              int,
  NoVertices            int,
  vertexID              int IDENTITY(1,1) NOT NULL primary key
);
GO
create table ctgl_vertices
(
  Id         int primary key,
  GeometryId int REFERENCES ctgl_geometry(vertexID),
  val        int
);
GO

Now once you have corrected the table schemas logically you will use SCOPE_IDENTITY() to get the latest identity value generated by the identity column in the ctgl_geometry table and use that value to insert rows into the ctgl_vertices table.

Something like......

Declare @NewID INT;

INSERT INTO ctgl_geometry (Id , otherVal, NoVertices)
VALUES (1, 10,5); 

 SET @NewID = SCOPE_IDENTITY();


INSERT INTO ctgl_vertices (Id, GeometryId  ,Val)
VALUES 
   (1,@NewID,0),
   (2,@NewID,1),
   (3,@NewID,6),
   (4,@NewID,5),
   (5,@NewID,4);

Upvotes: 1

Related Questions