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

Answer 2

Yes. To avoid this, I would make ID an int identity and make it alone the primary key and then add a new unique index on Username/AccountType.

Answer 3

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

You don't need that!

• put your PK on the ID column alone (it's an IDENTITY - so it's always unique) and use that to reference from child tables

• put a UNIQUE constraint (or index) on (UserName, AccountType) - that way you prevent a user from having two accounts with the same type

There's really no point in having AccountType in your PK, in my opinion.

Use this design instead:

TABLE: Accounts
  ID                 INT IDENTITY     PRIMARY KEY
  AccountType        INT          UNIQUE CONSTRAINT(Username, AccountType)
  Username           VARCHAR      UNIQUE CONSTRAINT(Username, AccountType)
  Password           VARCHAR