Primary/foreign key

Question

Currently I've normalized a couple of tables, but I've got some questions. To keep it simple:

I have 2 tables. 1: userAcces 2: userInformation

In userAcces "id" is the primary key, in userInformation "id" is the foreign key. How can I make sure when I insert something into both tables, the id will be/stay the same. So when I insert something they will automatically equal each other. So I can easily query it with a join statement.

EDIT Another question.

When you eventually delete some records for example id 1. How can you make sure all the records with id 1 will be deleted? And if you've deleted it, then the id order will look strange doesn't it?

For example you"le have :

id  name
3   james
6   Elona
9   Bryan

you insert into the parent table, let mysql generate an ID for it (e.g auto_increment), retrieve that id with `last_insert_id()`, then use that ID for your inserts into the child tables. — Marc B, May 11 '15 at 17:14
Sorry I used the wrong label. It's an mssql server. Can I still use that? — james, May 11 '15 at 17:15
You need to declare id in userAccess to be a foreign key with referential integrity to userInformation. That will ensure that only valid userInformation id's will be allowed to be inserted in userAccess — Tarik, May 11 '15 at 17:17
You can use http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id — Norbert, May 11 '15 at 17:18
Most if not all RDBMS's have this feature. Ms sql server have a GUI to help you do that. — Tarik, May 11 '15 at 17:19
For your edit, you'll want to use CASCADE DELETE. Be sure you understand this concept so you don't end up unintentionally deleting data! — Dave.Gugg, May 11 '15 at 18:10

score 0 · Answer 1 · answered May 11 '15 at 18:50

Here is an explanation using SQLFiddle.

Here is the code, just for completeness...

Setup:

CREATE TABLE ParentTable (
  ID int NOT NULL PRIMARY KEY,
  Name varchar(50)
)

CREATE TABLE ChildTable (
  ID int NOT NULL PRIMARY KEY,
  ParentID int NOT NULL FOREIGN KEY REFERENCES ParentTable(ID) ON DELETE CASCADE,
  Description varchar(50)
)

INSERT ParentTable VALUES ( 1, 'Bob');
INSERT ParentTable VALUES ( 2, 'Tim');

INSERT ChildTable VALUES ( 1, 1, 'Bob''s Description');
INSERT ChildTable VALUES ( 2, 2, 'Tim''s Description');

Examples:

SELECT * FROM ParentTable PT INNER JOIN ChildTable CT on CT.ParentID = PT.ID;

-- This will fail
INSERT ChildTable VALUES (3, 3, 'Judy''s Description');

-- It will succeed after inserting the Parent Record
INSERT ParentTable VALUES ( 3, 'Judy');
INSERT ChildTable VALUES (3, 3, 'Judy''s Description');
SELECT * FROM ParentTable PT INNER JOIN ChildTable CT on CT.ParentID = PT.ID;

-- Deleting the parent record will "cascade" the delete to the child table
DELETE ParentTable WHERE ID = 1;
SELECT * FROM ParentTable PT INNER JOIN ChildTable CT on CT.ParentID = PT.ID;

The Foreign Key constraint will enforce the referential integrity that you desire. The Cascading Delete will allow users to delete the parent and will automatically delete any referencing children. Without the cascading delete, any attempt to delete the parent would fail if the parent was referenced by any children.

Primary/foreign key

1 Answers1