0

I have a database with three tables. The table Authentication contains the following:

+----------+-----------------+------+-----+---------+----------------+
| Field    | Type            | Null | Key | Default | Extra          |
+----------+-----------------+------+-----+---------+----------------+
| id       | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| userid   | varchar(30)     | NO   |     | NULL    |                |
| password | varchar(30)     | NO   |     | NULL    |                |
| role     | varchar(20)     | NO   |     | NULL    |                |
| email    | varchar(50)     | YES  |     | NULL    |                |
+----------+-----------------+------+-----+---------+----------------+

Login contains the following:

+--------------+-----------------+------+-----+---------+----------------+
| Field        | Type            | Null | Key | Default | Extra          |
+--------------+-----------------+------+-----+---------+----------------+
| id           | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| TimeLoggedIn | text            | NO   |     | NULL    |                |
| sessionid    | varchar(255)    | NO   |     | NULL    |                |
+--------------+-----------------+------+-----+---------+----------------+

And Activity:

+----------+-----------------+------+-----+---------+-------+
| Field    | Type            | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+-------+
| id       | int(6) unsigned | NO   | PRI | NULL    |       |
| Torrents | mediumtext      | NO   |     | NULL    |       |
+----------+-----------------+------+-----+---------+-------+

There is a relation between the id fields of Authentication to id in the other tables.

I need to add multiple rows in Activity, with several values for Torrents for each id. Unfortunately, when I try adding a new row with duplicated id value with:

INSERT INTO `Activity` (`id`, `Torrents`) VALUES ('1', 'dssfsdffdsffs');

it gives me the error: #1062 - Duplicate entry '1' for key 'PRIMARY'

How do I solve it? How did I create the table wrong?

I've read the following apparently duplicate questions:

  1. #1062 - Duplicate entry for key 'PRIMARY'

But though it says to remove it as my primary key, mysql didnt allow me to create a relationship unless I made it a primary key.

Joel G Mathew
  • 7,561
  • 15
  • 54
  • 86
  • Isn't that what's supposed to happen? – Strawberry Jul 26 '17 at 09:09
  • @Strawberry Do you mean I cant have rows containing duplicate `id` value? – Joel G Mathew Jul 26 '17 at 09:10
  • 1
    Not if it's a PRIMARY KEY. Either id and torrents together form a natural PK, or you have a surrogate activity_id column. If you opt for a surrogate key then, typically, a UNIQUE KEY would be formed on remaining columns. – Strawberry Jul 26 '17 at 09:10
  • @Strawberry I was unable to create a relationship if I didnt make it a primary key. So how do I address it? Make another field primary? Can I still create a relationship to the id field? – Joel G Mathew Jul 26 '17 at 09:11

1 Answers1

1

You cannot initiate one-to-many relation by referring primary key to primary key. That'll be a one-to-one relationship.

In both your Login and Activity tables you need to have a foreign key to refer back to Authentication's ID. Example:

 CONSTRAINT `FK_Login` FOREIGN KEY (`AuthenticationID`) REFERENCES `Authentication` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
Amao Mao
  • 51
  • 5
  • So what should be the properties of this new field AuthenticationID? – Joel G Mathew Jul 26 '17 at 09:28
  • 1
    It should be the same as the key you want to refer. So if your Authentication.ID is int(6) unsigned NOT NULL, the foreign keys in Login and Activity must also be int(6) unsigned NOT NULL. – Amao Mao Jul 26 '17 at 09:36
  • @Droidzone Note however that the number inside the parentheses has (almost) no meaning in MySQL, so that argument can normally be omitted. – Strawberry Jul 26 '17 at 10:59