Home › Forums › Back End › How to structure database for portfolio pieces on profile pages › Reply To: How to structure database for portfolio pieces on profile pages
I need each skill to be unique for each user id
skill_id the id of the user? (I wouldn’t think so, but that’s what your example makes it look like.)
Anyway, whatever the user id is, make it a compound primary key along with the skill. So, something like:
create table user_skills( user_id bigint unsigned not null, skill varchar(50) not null, skill_rating int default null, primary key( user_id,skill ), foreign key( user_id ) references user( id ) )engine=innodb;
insert into user_skills (user_id,skill) values( 1,"HTML" ), -- okay ( 1,"CSS" ), -- okay ( 2,"HTML" ), -- okay ( 1,"HTML" ) -- ERROR 1062 (23000): Duplicate entry '1-HTML' for key 'PRIMARY'