CODEWITHSUNDEEP
phpmysqlsqlsql-server
Madeline Ries
Madeline Ries

Reputation: 629

Confusion between PK and FK table design

I have a person table and a score table. The Person table basically stores a person's information while score table stores what kind of score a person has. I set the FK constraint in score table to ON DELETE: CASCADE 

person
- id
- name
- scored_id (FK)

score
- id (PK)
- bmi
- weight

So, in the table setting score.id is linked with person's scored_id. That being said when I delete a record in score, a person will get deleted as well. But why when I delete a record in person, the record of his in score is not deleted?

Upvotes: 0

Views: 69

Answers (3)

Professor Abronsius
Professor Abronsius

Reputation: 33813

Just an idea how you might structure the tables and use a foreign key which will delete records from the score table if/when a user from the person table is deleted. The score table should have a reference to the user - pid which is used as the foreign key dependancy. It makes sense to me that the score is dependant upon the user so no user, no score.

create table `person` (
    `id` int(10) unsigned not null auto_increment,
    `name` varchar(50) null default null,
    primary key (`id`)
)
collate='latin1_swedish_ci'
engine=innodb
auto_increment=4;


mysql> describe person;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+




create table `score` (
    `id` int(10) unsigned not null auto_increment,
    `bmi` int(10) unsigned not null default '0',
    `weight` int(10) unsigned not null default '0',
    `pid` int(10) unsigned not null default '0',
    primary key (`id`),
    index `pid` (`pid`),
    constraint `fk_sc_pid` foreign key (`pid`) references `person` (`id`) on update cascade on delete cascade
)
collate='latin1_swedish_ci'
engine=innodb
auto_increment=4;



mysql> describe score;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| bmi    | int(10) unsigned | NO   |     | 0       |                |
| weight | int(10) unsigned | NO   |     | 0       |                |
| pid    | int(10) unsigned | NO   | MUL | 0       |                |
+--------+------------------+------+-----+---------+----------------+





mysql> select * from person;
+----+------+
| id | name |
+----+------+
|  1 | bob  |
|  2 | rita |
|  3 | sue  |
+----+------+


mysql> select * from score;
+----+-----+--------+-----+
| id | bmi | weight | pid |
+----+-----+--------+-----+
|  1 |  34 |     34 |   1 |
|  2 |  56 |     41 |   2 |
|  3 |  56 |     77 |   3 |
+----+-----+--------+-----+



mysql> delete from person where id=3;
Query OK, 1 row affected (0.00 sec)

/* delete a user, the score disappears too which makes sense */

mysql> select * from score;
+----+-----+--------+-----+
| id | bmi | weight | pid |
+----+-----+--------+-----+
|  1 |  34 |     34 |   1 |
|  2 |  56 |     41 |   2 |
+----+-----+--------+-----+

Upvotes: 2

Lew Perren
Lew Perren

Reputation: 1239

Your issue is semantic understanding of the task, rather than syntax. Intuitively your relation looks wrong. It is unlikely, that a particular score, say 75kg and bmi of 20 will need to have a many relations link to people with the same score. This would be arbitary. More likely, your want, a person to have different scores over time, then when you delete a person, you want their associated values deleted. So table relation should be:

person
- id (Primary Key)
- name

score
- id (Primary Key)
- bmi
- weight
- scoreDate
- personID (Foreign Key to person)

A score date would be a helpful addition.

This structure will allow a person to have a history of many score and see the fluctuation of their weight and body mass index over time. A semantically helpful task that resonates with reality, and therefore follows the notions of entity analysis and table structures following the real world application.

Helpful discussion of ERD and table structure levels and relations

Upvotes: 1

Lalmani Dewangan
Lalmani Dewangan

Reputation: 308

In you tables, "person" table is having reference(FK) of "score" table so when you delete a record in "score" table mysql search related record in "users" table to delete.

but "score" table dose not have any reference(FK) of "person" table.

You can try below table structure if you want to delete score record when person record will be delete but person record will be still safe if score record will be delete

person
- id (PK)
- name

score
- id (PK)
- person_id (FK)
- bmi
- weight

Upvotes: 0

Related Questions