mysql - Referencing table with generated primary keys -

i trying increase constraint of mysql database schema adding foreign key constraint each table.

table 1: users

    +---------+----------+-------------     | id      | username | other fields     +---------+----------+-------------     | 1       | john     |     | 2       | mark     |     +---------+----------+------------- 

id int(11) unsigned not null auto_increment

username` varchar(50) not null

primary key (id)

table 2: disks (this has one many relationship users)

    +---------+----------+-----------+-------------     | id      | id_user  | disk_name | other fields     +---------+----------+-----------+-------------     | 1       | 1        | disk    |     | 2       | 2        | disk b    |     +---------+----------+-----------+------------- 

id int(11) unsigned not null auto_increment

id_user int(11) not null,

primary key (id,id_user) index fk_disks_idx (id asc)

constraint fk_disks

foreign key (id)

references database.users (id)

on delete no action

on update no action)

table 3: files (this has one many relationship disks)

    +---------+----------+----------+-----------+-------------     | id      | id_disk  | id_user  | file_name | other fields     +---------+----------+----------+-----------+-------------     | 1       | 1        | 1        |           |     | 2       | 2        | 2        |           |     +---------+----------+----------+-----------+------------- 

id int(11) unsigned not null auto_increment

id_user int(11) not null

id_disk int(11) not null

primary key (id,id_disk,id_user ) index fk_files_idx (id asc, id_user asc)

constraint fk_files

foreign key (id_disk, id_user, id_user)

references database.disks (id)

on delete no action

on update no action)

table 2: files_on_nas (this has one one relationship files)

    +-------+----------+----------+----------+-----------+-------------     | id    | id_files | id_user  | id_disk  | file_name | other fields     +-------+----------+----------+----------+-----------+-------------     | 1     | 1        | 1        | 1        |           |     | 2     | 1        | 2        | 2        |           |     +-------+----------+----------+----------+-----------+------------- 

id int(11) unsigned not null auto_increment

id_files int(11) not null,

id_user int(11) not null,

id_disk int(11) not null,

primary key (id,id_files,id_user,id_disk ) index fk_files_on_nas_idx (id asc)

constraint fk_files_on_nas

foreign key (id_files,id_user,id_disk)

references database.files (id,id_user, id_disk)

on delete no action

on update no action)

question:

as can see more reference table in cascade more primary keys get. how can design database avoid replication of primary keys , therefore data duplication well? should delete auto incremented key each table? practice?

thanks

the id of disk sufficient uniquely identify disk. there's no reason include id of user disk's primary key. extremely bad idea, because means if disk's user changes, need modify primary key.

same file. file id uniquely identifies file. there's no reason add disk id primary key of file.