CODEWITHSUNDEEP
mysqldatabaseforeign-keysprimary-key
Rav
Rav

Reputation: 1193

Can a table have two foreign keys?

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

Customer table

Account Category table

Customer Detail table

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

Receivable

Receivable Account

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?

Upvotes: 33

Views: 261521

Answers (5)

user319198
user319198

Reputation:

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

Get more details here FOREIGN KEY Constraints

Upvotes: 41

Rutvik Mehta
Rutvik Mehta

Reputation: 11

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

Upvotes: 0

momo
momo

Reputation: 1

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

Upvotes: -1

user2915443
user2915443

Reputation: 429

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

Upvotes: 42

Marcelo Cantos
Marcelo Cantos

Reputation: 185852

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.

Upvotes: 4

Related Questions