Can a table have two foreign keys?

Question

I have the following tables (Primary key in bold. Foreign key in Italic)

Customer table

ID---Name---Balance---Account_Name---Account_Type

Account Category table

Account_Type----Balance

Customer Detail table

Account_Name---First_Name----Last_Name---Address

Can I have two foreign keys in the Customer table and how can I implement this in MySQL?

Updated

I am developing a web based accounting system for a final project.

Account Category

Account Type--------------Balance

Assets
Liabilities
Equity
Expenses
Income

Asset

Asset_ID-----Asset Name----Balance----Account Type

Receivable

Receivable_ID-----Receivable Name-------Address--------Tel-----Asset_ID----Account Type

Receivable Account

Transaction_ID----Description----Amount--- Balance----Receivable_ID----Asset_ID---Account Type

I drew the ER(Entity relationship) diagram using a software and when I specify the relationship it automatically added the multiple foreign keys as shown above. Is the design not sound enough?

Add a quick Google search reveals: http://ben.lobaugh.net/blog/135/mysql-create-a-unique-composite-key — Tieson T., Mar 14 '12 at 05:49
A table can have multiple foreign keys and no composite keys. A composite key simply means that there are two or more columns making up the key value. The set of columns in a foreign key references the values in a set of columns in another table (or, exceptionally, of another set of columns in the same table). And a table can have a composite key that contains no foreign key columns, and can have multiple foreign keys that are not a part of any composite key. — Jonathan Leffler, Mar 14 '12 at 06:28
I'm not convinced that the design is sound, but that's not because of the multiple foreign keys. I'm not clear if you have two unique keys (ID and Name) in the customer table, or one composite primary key. Normally, if you have an ID column, that is unique. Names are notoriously 'not unique' in the real world. You should probably make the Customer.ID column into the primary key of that table. I'm not sure what a customer's balance means; normally, accounts have balances. There isn't an Account table shown, which is surprising. An account might have an account name (and number)... _cont'd_ — Jonathan Leffler, Mar 14 '12 at 06:47
_cont'd_... and would also perhaps contain a customer ID. The Account Category table sounds a little surprising; it would not normally have a balance per se (that would belong in the Account Table). The Customer Detail table would probably have a Customer ID in it to reference the Customer table, rather than the Customer Detail table defining the Account Name in the Customer table. Customers can usually have multiple accounts (checking - or chequing - account and a savings account, for example). All this analysis is, though, independent of the headline question. The answer to that is **yes**. — Jonathan Leffler, Mar 14 '12 at 06:52

score 43 · Answer 1 · edited Oct 24 '13 at 11:25

create table Table1
(
  id varchar(2),
  name varchar(2),
  PRIMARY KEY (id)
)


Create table Table1_Addr
(
  addid varchar(2),
  Address varchar(2),
  PRIMARY KEY (addid)
)

Create table Table1_sal
(
  salid varchar(2),`enter code here`
  addid varchar(2),
  id varchar(2),
  PRIMARY KEY (salid),
  index(addid),
  index(id),
  FOREIGN KEY (addid) REFERENCES Table1_Addr(addid),
  FOREIGN KEY (id) REFERENCES Table1(id)
)

score 39 · Accepted Answer · edited Apr 16 '21 at 06:37

39

Yes, MySQL allows this. You can have multiple foreign keys on the same table.

Get more details here FOREIGN KEY Constraints

edited Apr 16 '21 at 06:37

Siegfrid Haberman

25
1
6

answered Mar 14 '12 at 05:59

2

You should accept answers which helped you. Please accept your previous answers too it will he helpful for other users facing same problem. – Mar 14 '12 at 06:22
Just to be clear: Making a composite key has nothing to do with having two foreign keys. See Jonathan's comment on the question. – nvogel Mar 14 '12 at 06:32
agree with @Jonathan. But I just purposed to make composite key is different keys on same column. It does't mean composite key and having two foreign keys are same. Updated my answer also – Mar 14 '12 at 06:35
@Teez: Whatever it is you're purposing (sic), it doesn't answer the question, which has nothing to do with composite keys, normal or foreign. – Marcelo Cantos Mar 14 '12 at 06:47
It wasn't clear to me why this was the accepted answer, simply because the link doesn't explicitly mention you can have multiple foreign keys, nor given an example, whereas another answer below has an actual example with multiple foreign keys. – Marcus Jan 16 '19 at 17:07

Marcelo Cantos · Answer 3 · 2012-03-14T06:56:38.680

The foreign keys in your schema (on Account_Name and Account_Type) do not require any special treatment or syntax. Just declare two separate foreign keys on the Customer table. They certainly don't constitute a composite key in any meaningful sense of the word.

There are numerous other problems with this schema, but I'll just point out that it isn't generally a good idea to build a primary key out of multiple unique columns, or columns in which one is functionally dependent on another. It appears that at least one of these cases applies to the ID and Name columns in the Customer table. This allows you to create two rows with the same ID (different name), which I'm guessing you don't want to allow.

score 0 · Answer 4 · answered Feb 23 '21 at 11:36

0

Yes, a table have one or many foreign keys and each foreign keys hava a different parent table.

answered Feb 23 '21 at 11:36

Rutvik Mehta

11
2

score -1 · Answer 5 · edited Jul 19 '19 at 06:42

CREATE TABLE User (
user_id INT NOT NULL AUTO_INCREMENT,
userName VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
userImage  LONGBLOB NOT NULL, 
Favorite VARCHAR(255) NOT NULL,
PRIMARY KEY (user_id)
);

and

CREATE TABLE Event (
    EventID INT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (EventID),
    EventName VARCHAR(100) NOT NULL,
    EventLocation VARCHAR(100) NOT NULL,
    EventPriceRange VARCHAR(100) NOT NULL,
    EventDate Date NOT NULL,
    EventTime Time NOT NULL,
    EventDescription VARCHAR(255) NOT NULL,
    EventCategory VARCHAR(255) NOT NULL,
    EventImage  LONGBLOB NOT NULL,     
    index(EventID),
    FOREIGN KEY (EventID) REFERENCES User(user_id)
);

Can a table have two foreign keys?

Customer table

Account Category table

Customer Detail table

5 Answers5

Linked