CODEWITHSUNDEEP
mysqldatabaseforeign-keys
Reaven Smith
Reaven Smith

Reputation: 359

confused with mysql foreign keys

I am a little confused with foreign keys and how they are supposed to work.

i have 3 tables in database

database = mydb
table parent = user
table child = hobbies
table child2 = skills

*mydb.user have apID as primary and Auto Increment
*mydb.hobbies have apID with relation with mydb.user apID
and hID as auto increment 
*mydb.skills hace apID with relation with mydb.user apID
and sID as auto increment

I am confused on how to insert data so that the apID in hobbies and skills are the same? Which key in hobbies and skills need to be primary, the foreign or the other?

ok I end up with 

INSERT INTO `mydb`.`user` (`appID`, `name`) VALUES (NULL, 'pedro');
INSERT INTO `skills` (`appID`,`dipID`,`name``) VALUES(LAST_INSERT_ID(),NULL,'running')

Upvotes: 2

Views: 418

Answers (4)

Devart
Devart

Reputation: 122032

You have next tables -

CREATE TABLE `USER`(
  APID INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (APID)
) ENGINE = INNODB AUTO_INCREMENT = 1;

CREATE TABLE HOBBIES(
  HID INT(11) NOT NULL AUTO_INCREMENT,
  APID INT(11) DEFAULT NULL,
  PRIMARY KEY (HID),
  CONSTRAINT FK_HOBBIES_USER_APID FOREIGN KEY (APID) REFERENCES `USER` (APID)
    ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = INNODB AUTO_INCREMENT = 1;

CREATE TABLE SKILLS(
  SID INT(11) NOT NULL AUTO_INCREMENT,
  APID INT(11) DEFAULT NULL,
  PRIMARY KEY (SID),
  CONSTRAINT FK_SKILLS_USER_APID FOREIGN KEY (APID) REFERENCES `USER` (APID)
    ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = INNODB AUTO_INCREMENT = 1;

Our ID primary fields are declared as AUTO_INCREMENT, so it is better to use this feature - add NULL value and MySQL will generate new ID value itself (1, 2, 3 and so on) -

-- Add first user and all his properties (hobbies, skills)
INSERT INTO user(apID) VALUES(NULL); -- We pass NULL

-- But real ID is 1, we set this value to variable @id
-- This @id will be used to insert correct foreign key values in child tables
SET @id = LAST_INSERT_ID();

-- Add some hobbies and skills for user 1
INSERT INTO hobbies(hID, apID) VALUES (NULL, @id);
INSERT INTO hobbies(hID, apID) VALUES (NULL, @id);
INSERT INTO hobbies(hID, apID) VALUES (NULL, @id);

INSERT INTO skills(sID, apID) VALUES (NULL, @id);
INSERT INTO skills(sID, apID) VALUES (NULL, @id);
INSERT INTO skills(sID, apID) VALUES (NULL, @id);

Do the same for each user.

Check results:

SELECT * FROM `user`;
+------+
| APID |
+------+
|    1 |
+------+

SELECT * FROM hobbies;
+-----+------+
| HID | APID |
+-----+------+
|   1 |    1 |
|   2 |    1 |
|   3 |    1 |
+-----+------+

SELECT * FROM skills;
+-----+------+
| SID | APID |
+-----+------+
|   1 |    1 |
|   2 |    1 |
|   3 |    1 |
+-----+------+

Upvotes: 1

Windle
Windle

Reputation: 1392

The primary key is the column(s) that uniquely identify a row in a table. For your user table it is just apID and for the hobbies and skills tables it is either just hID and sID or hID + apID and sID + apID (depends on what your data represents).

A foreign key is a refence to a value in another table. So if you have a foreign key constraint on apID in hobbies that refences apID in user, that means that every apID in hobbies must have a matching apID in user. If you try to insert or update a row in hobbies or user so that a matching pair goes away MySQL will throw an error.

The hobbies table might look like this:

CREATE TABLE hobbies (
  hID INT(10) AUTO_INCREMENT NOT NULL,
  apID INT(10),
  PRIMARY KEY (hID, apID),
  FOREIGN KEY (apID) REFERENCES user(apID) ON UPDATE CASCADE
);

The neat thing there is the ON UPDATE CASCADE clause. That will make it so that if you update an apID in user, the database will update any rows it has to in hobbies so that everything stays in synch. There are also things like ON DELETE CASCADE, but if you do a little research those should be easy to find.

As for how to insert the data, you do it as you normally would. The only caveat is that before you can insert into hobbies or skills, there has to be a matching apID already in the user table.

Here is a SQL Fiddle that shows all this stuff in action.

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13506

1.table parent = user Here apID is primary key

2.table child = hobbies here hID is primary key and apID is foreign key

3.table child2 = skills here sID is primary key and apID is foreign key

--INSERT COMMANDS

1.INSERT INTO user

2.INSERT INTO hobbies here you have to select apID from users table

3.INSERT INTO skills here you have to select apID from users table

Upvotes: 0

Seismoid
Seismoid

Reputation: 146

sorry but what you wrote is a little bit confusing. i hope i understood your problem.

if you have a table user with primary key userID, and two tables hobbies and skills that use user.userID as foreign keys. You will need a primary key in both hobbies and skills (e.g. hobbiesID and skillsID). userID can't be the primary key of hobbies/skills, because it would not be unique; it is just used as foreign key to form the relation between the usertable and the hobbies/skillstables.

Upvotes: 0

Related Questions