CODEWITHSUNDEEP
mysqlsqlforeign-keysprimary-key
user3521471
user3521471

Reputation: 180

MySql Can i use a foreign key as PART of my primary key?

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)
    -> )
    -> ;

Upvotes: 0

Views: 86

Answers (1)

Rahul
Rahul

Reputation: 77926

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

Upvotes: 1

Related Questions