CODEWITHSUNDEEP
mysqlrelational-database
Joel G Mathew
Joel G Mathew

Reputation: 8081

Properly creating a relational database

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.

Upvotes: 0

Views: 78

Answers (1)

Amao Mao
Amao Mao

Reputation: 51

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

Upvotes: 1

Related Questions