0

I something like this valid?

These are the columns in my ACCOUNTS table:

user_id (Primary Key)
user_name
user_pass
user_email
user_date

Accounts are allowed to have multiple characters. Heres how i want to write that table: CHARACTERS table:

character_ID
user_ID (foreign key)
character_name
...(random columns)

Can i make my PRIMARY KEY (character_ID, user_ID)? Or is there a better way to distinguish this?

EDIT: here is how i would create the table:

CREATE TABLE characters(
    -> char_id INT(8) NOT NULL AUTO_INCREMENT,
    -> char_name VARCHAR(50) NOT NULL,
    -> user_id INT(8) 
    -> PRIMARY KEY(char_id, user_id),
    -> FOREIGN KEY (user_id) REFERENCES users(user_id)
    -> )
    -> ;
user3521471
  • 180
  • 1
  • 12
  • show create table for `characters`. – Ravinder Reddy Jun 07 '14 at 18:03
  • Yes you can, then it becomes a composite key. – Rahul Jun 07 '14 at 18:03
  • @Ravinder I havnt done it yet but i edited my OP to add how i think i would do it. – user3521471 Jun 07 '14 at 18:09
  • Yes, that would work as you have it. I usually like to keep the primary key at the front for readability though. For example, have the order: char_id, user_id, char_name that way it is easier to distinguish the primary key. – Devon Bessemer Jun 07 '14 at 18:14
  • @Devon okay thanks :) My next question would be, how do i create the table to have char_id increment for each user_id. IE: user 1 has three characters. User 1's character ids are 1, 2 and 3. User 2 has 2 characters. User 2's character ids are 1, and 2. The primary keys would still be different. – user3521471 Jun 07 '14 at 18:16
  • If you want to do that, you have to program the ID generation yourself, it can't be done with `auto_increment`. Why do you need to do that? Why not just use unique sequential IDs for everyone, and use `auto_increment`? – Barmar Jun 07 '14 at 18:19
  • Here is a link to a discussion on something similar: http://stackoverflow.com/questions/18120088/defining-composite-key-with-auto-increment-in-mysql. In short, you can't with the table structure, you would need to setup a procedure, calculate it in the INSERT statement, or generate it in the logic of your application (PHP, etc) – Devon Bessemer Jun 07 '14 at 18:19

1 Answers1

1

Yes you can make both (char_id, user_id) as primary key in characters table and then it becomes a composite key but foreign key allowed to have duplicates, because of which they may not be best to use as a Primary Key.

So generally it's recommended and best practice to use a field that can uniquely determine all other fields in a record and designate that as primary key.

It can be a synthetic one like (auto_increment in MySQL, IDENTITY in SQL Server). or it can be normal/general like "Social Security Number" etc.

So, in your case if char_id can alone uniquely determine all other fields then I would make it as PK char_id INT NOT NULL AUTO_INCREMENT primary key

Rahul
  • 76,197
  • 13
  • 71
  • 125