Does a table with a foreign key need a primary key?

Can a foreign key be used as a primary key?

It is perfectly fine to use a foreign key as the primary key if the table is connected by a one-to-one relationship, not a one-to-many relationship. If you want the same user record to have the possibility of having more than 1 related profile record, go with a separate primary key, otherwise stick with what you have.

Can a table exist only with foreign key?

There is no problem having a table that consists of foreign keys only. In your particular example it would be OK to remove the OrderID. Relational theory states that each table should have a candidate key so, ideally, the business rule you mention would be in place to make the foreign key only table a "good" table.

Can the foreign key be a non primary key?

Answers. Yes. You can FK reference any UNIQUE KEY constraint (one or more columns).