Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

can a data table contain more than one foreign key with same id?

Dear experts,

I have a question concerning with mysql data tables.

Here is the scenario. I have three tables. One table contains information of local quotes. Another table contains information of oversea quotes. I wish to setup two foreign key constraints in order table which can handle ids from local and oversea order tables.

here is my sample tables (DDL):

local table:

id   int auto_increment,
quote_header int,
cust_no varchar(10),
src_tbl varchar(1),
...... (there is more columns)

oversea table:

id   int auto_increment,
quote_header int,
cust_no varchar(10),
src_tbl varchar(1),
...... (there is more columns)

local and oversea table structure are not exactly the same (slight different in column counts and name)

Is it possible that I can create foreign key constraints in order table like this?

ALTER TABLE orders ADD FOREIGN CONSTRAINTS oversea_order_fk FOREIGN KEY (ref_id, src_tbl) REFERENCES oversea (id, src_tbl)
ALTER TABLE orders ADD FOREIGN CONSTRAINTS local_order_fk FOREIGN KEY (ref_id, src_tbl) REFERENCES local (id, src_tbl)

The src_tbl column is used for referencing to either local or the oversea table because both local and oversea table can have an auto number of 1. If I have a composite key then I can have two #1s existing in orders table that can be
distinguished by src_tbl column as in 'O' for oversea and 'L' for local. Example will be:

order table:

id int,
ref_id int
src_tbl

id       ref_id      src_tbl
1            1            O  ---> refers to row 1 in oversea table
2            1             L ----> refers to row 1 in local table

If not possible what else can I do in order to reference order's ref_id with local and oversea tables?

Let me know if anyone needs more data in order to have a better picture of this situation.

Thanks.
0
Kinderly Wade
Asked:
Kinderly Wade
  • 2
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
It's not possible.
You could create the fk the other way round, but I think the iblt real way is to avoid the table layout like that.
Create only 1 table for the orders, with a flag column (overseas or not). You may have some additional tables for specific data/columns and put the fk indeed from that table pointing to the main order table.
This design will also simplify further layout for other related tables to the orders...
0
 
ste5anSenior DeveloperCommented:
Basically it works. Well, don't have a MySQL at hand to test fir correct syntax.

But:
This kind of concept is used to express a strict super-sub classing concept. So we should do it semantically correct. Orders are not a super class of quotes.
So create an Quotes table. Let Orders point to this Quotes table. Now the FK in Orders requires only the ID and not the sub class discriminator.
The Quotes table has all common columns of the old local and overseas quotes tables. Here we implement the discriminator.

Orders: OrderID, [..] QuoteID
PK OrderID
FK QuoteID -> Quotes(QuoteID)

Quotes: QuoteID, QuoteTypeCode, [..], Common Columns
PK QuoteID, QuoteTypeCode
QuoteTypeCode must contain 'L' or 'O'.

LocalQuotes: QuoteID, QuoteTypeCode , [..], Local columns
PK QuoteID, QuoteTypeCode
FK QuoteID, QuoteTypeCode -> Quotes(QuoteID, QuoteTypeCode)
QuoteTypeCode must contain 'L'.

OverseaQuotes: QuoteID, QuoteTypeCode , [..], Oversea columns
PK QuoteID, QuoteTypeCode
FK QuoteID, QuoteTypeCode -> Quotes(QuoteID, QuoteTypeCode)
QuoteTypeCode must contain 'O'.

Now we have a Quotes super class and Local and Overseas as sub classes semantically defined.

The "ugly" part is:
As there is no CHECK constraint in MySQL we need three helper tables to implement them.
One for Quotes which contains L and O and Quotes has a FK to it.
One for LocalQuotes which only contains L and LocalQuotes has a FK to it.
One for OverseaQuotes which only contains O and OverseaQuotes has a FK to it.
0
 
ste5anSenior DeveloperCommented:
Here is a sample on SQL Fiddle of it.

DDL:
CREATE TABLE Quotes_QuoteTypeCodes (
  QuoteTypeCode CHAR(1) PRIMARY KEY
);

CREATE TABLE Quotes ( 
  QuoteID INT NOT NULL AUTO_INCREMENT,
  QuoteTypeCode CHAR(1) NOT NULL,
  Payload VARCHAR(100),
  PRIMARY KEY (QuoteID, QuoteTypeCode),
  FOREIGN KEY (QuoteTypeCode) REFERENCES Quotes_QuoteTypeCodes(QuoteTypeCode)
);

CREATE TABLE LocalQuotes_QuoteTypeCodes (
  QuoteTypeCode CHAR(1),
  SingleRowOnly ENUM('') NOT NULL PRIMARY KEY,
  UNIQUE (QuoteTypeCode)
);

CREATE TABLE LocalQuotes ( 
  QuoteID INT NOT NULL AUTO_INCREMENT,
  QuoteTypeCode CHAR(1) NOT NULL,
  Payload VARCHAR(100),
  PRIMARY KEY (QuoteID, QuoteTypeCode),
  FOREIGN KEY (QuoteID, QuoteTypeCode) REFERENCES Quotes(QuoteID, QuoteTypeCode),
  FOREIGN KEY (QuoteTypeCode) REFERENCES LocalQuotes_QuoteTypeCodes(QuoteTypeCode)
);

CREATE TABLE OverseaQuotes_QuoteTypeCodes (
  QuoteTypeCode CHAR(1),
  SingleRowOnly ENUM('') NOT NULL PRIMARY KEY,
  UNIQUE (QuoteTypeCode)
);

CREATE TABLE OverseaQuotes ( 
  QuoteID INT NOT NULL AUTO_INCREMENT,
  QuoteTypeCode CHAR(1) NOT NULL,
  Payload VARCHAR(100),
  PRIMARY KEY (QuoteID, QuoteTypeCode),
  FOREIGN KEY (QuoteID, QuoteTypeCode) REFERENCES Quotes(QuoteID, QuoteTypeCode),
  FOREIGN KEY (QuoteTypeCode) REFERENCES OverseaQuotes_QuoteTypeCodes(QuoteTypeCode)
);

CREATE TABLE Orders ( 
  OrderID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  QuoteID INT NOT NULL,
  Payload VARCHAR(100),
  FOREIGN KEY (QuoteID) REFERENCES Quotes(QuoteID)
);
  
INSERT INTO Quotes_QuoteTypeCodes (QuoteTypeCode) VALUES ( 'L' );
INSERT INTO Quotes_QuoteTypeCodes (QuoteTypeCode) VALUES ( 'O' );
INSERT INTO LocalQuotes_QuoteTypeCodes (QuoteTypeCode) VALUES ( 'L' );
INSERT INTO OverseaQuotes_QuoteTypeCodes (QuoteTypeCode) VALUES ( 'O' );

INSERT INTO Quotes (QuoteTypeCode, Payload) VALUES ( 'L', 'a local' );
INSERT INTO Quotes (QuoteTypeCode, Payload) VALUES ( 'O', 'a oversea' );

INSERT INTO LocalQuotes (QuoteID, QuoteTypeCode, Payload) VALUES ( 1, 'L', 'its local' );
INSERT INTO OverseaQuotes (QuoteID, QuoteTypeCode, Payload) VALUES ( 2, 'O', 'from oversea' );

INSERT INTO Orders (QuoteID, Payload) VALUES ( 1, 'local' );
INSERT INTO Orders (QuoteID, Payload) VALUES ( 2, 'oversea' );

Open in new window

DML:
SELECT * 
FROM Orders O
  INNER JOIN Quotes Q ON Q.QuoteID = O.QuoteID;

SELECT O.*,
  Q.Payload AS QuotePayload, 
  LQ.Payload AS LocalQuotePayload, 
  OQ.Payload AS OverseaQuotePayload
FROM Orders O
  INNER JOIN Quotes Q ON Q.QuoteID = O.QuoteID
  LEFT JOIN LocalQuotes LQ ON LQ.QuoteID = Q.QuoteID
  LEFT JOIN OverseaQuotes OQ ON OQ.QuoteID = Q.QuoteID;

Open in new window


Just a reminder:

While we physically implemented Quotes_QuoteTypeCodes LocalQuotes_QuoteTypeCodes, OverseaQuotes_QuoteTypeCodes with foreign keys to their quotes table, these constraints are CHECK constraints. So when you use named constraints - which I encourage to do - then name them accordingly. I use the CK prefix for it.

Another problem may arise when you use object relational mappers on this kind of structure. L and O are constant sets. This should be reflected in the classes. The helper tables must not be used here. Cause the discriminator is implicit represented by the classes.
0
 
Kinderly WadeprogrammerAuthor Commented:
Thank you Guy Hengel and ste5an. Those are the answers that I am looking for. I just raised the points which I can allocate more points for you guys. Great answers.
0
 
Kinderly WadeprogrammerAuthor Commented:
Thank you Guy Hengel and ste5an. Those are the answers that I am looking for. I just raised the points which I can allocate more points for you guys. Great answers. Both repliers has great solution because Guy Hengel points out that it is not possible with my method but there is a work around and generally described the work around. Ste5an actually goes into detail with the work around which stated clearly how it shall be done. These answers save me time and point me at the right direction. Thanks again.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now