Composite keys as Foreign Key?

Question

I have the following table...

TABLE: Accounts
  ID (int, PK, Identity)
  AccountType (int, PK)
  Username (varchar)
  Password (varchar)

I have created a composite key out of ID and AccountType columns so that people can have the same username/password but different AccountTypes.

Does this mean that for each foreign table that I try and link to I'll have to create two columns?

I’m using SQL Server 2008

Answer 1

Does this mean that for each foreign table that I try and link to I'll have to create two columns?

Yes, you will.

However, it will be better to use a separate table to store username / password and link this table to user_accounts (with a composite primary key).

I have created a composite key out of ID and AccountType columns so that people can have the same username/password but different AccountTypes.

With you current design, people with the same id but different AccountType can have different usernames and passwords.

Here's how you should make it:

CREATE TABLE t_user (id INT NOT NULL IDENTITY PRIMARY KEY, Username VARCHAR(100) NOT NULL, YouShouldAlwaysUseSaltedAndHashedPasswords VARCHAR(64) NOT NULL)

CREATE TABLE t_user_account (user_id INT NOT NULL, account_id INT NOT NULL, PRIMARY KEY (user_id, account_id), FOREIGN KEY (user_id) REFERENCES t_user (user_id))