CODEWITHSUNDEEP
sql-server
LimYX
LimYX

Reputation: 45

Handling Foreign Key Data

I'm new SQL in general and the structure/design of my database could be the issue. I am unsure on how to update the value of the foreign key when inserting a new row of data for Table 1 from my windows form application(VB.NET). When I insert the data what if there's more keys than users? I assume since foreign keys are based off on primary keys of another table in this case user_id I can't possibly have a higher user_id value in user_id (FK) than user_id (PK).

Example:

Table 1

key_id(PK) | key_name | key_info | access_group | user_id(FK)
-----------+----------+----------+--------------+-----------
        1  |    key 1 | someinfo |      group 1 |          1
        2  |    key 2 | someinfo |      group 2 |          1
        3  |    key 3 | someinfo |      group 3 |          1
        4  |    key 4 | someinfo |      group 1 |          1

Table 2

user_id(PK) | user_name | user_contact | user_address  | access_permission
------------+-----------+--------------+---------------+------------------
          1 |     Bryan |     11111111 | sampleaddress | group 1

Edit: the idea is that there are access groups for the keys so that way users only have access to certain groups of keys.

Upvotes: 0

Views: 79

Answers (1)

MatrixTXT
MatrixTXT

Reputation: 2502

May be I can make a simple example

Lets say we have Table 2

like this

user_id(PK) | user_name
         1           A
         2           B
         4           C
         5           D

Please Notice that to use Foreign key in other table, you need to have Table 2 set up first.

Then, you start to create the Table 1(key_id(PK) | key_name | user_id(FK)) that with Foreign key in user_id.

To safely add item to Table 2, you need query like this.

insert into Table 1 (key_name, user_id)
select *some_name, user_id from Table 2
where ....

This query won't insert any user_id not in Table 2, therefore it is safe.

So what will make you Bloom~?

Let's say I insert the Table 1 with arbitrary user_id.

insert into Table 1 (key_name, user_id)
values (*some_name, 3)

Oops, 3 is not in Table 2, sql will raise the error, and stop you from doing this.

Upvotes: 1

Related Questions