How to a foreign key points to a composed primary key?

I have a table Login which has login and password fields. Both are primary key.

And I have a table Character. Which login can have many characters. So how does the foreign key works?

How do I create foreign key inside Character table to Login?

Should I create 2 fields as foreign k开发者_开发技巧eys inside Character table to point to Login?

Thanks!

Use the following in your table creation or alter script:

create table character (
  field1 varchar(30),
  ...,
  login varchar(12),
  password varchar(128),
  foreign key (login, password) references login (login, password)
)

Now, this said, it seems odd to include password in your primary key of your login table. I'd recommend just having a primary key on the login table, or perhaps even a surrogate key (integer identity). Using the approach of including login and password, you'll allow multiple signups under the same login. That may be okay, but it seems like you could actually create a situation where a single user accidentally creates multiple logins. Also, it will make it more difficult to manage lost password recovery. Lastly, each time that the password changes (and the user could change it at any time), it will have to be updated in each referring field, and will drastically complicate password changes.