How to make composite key using foreign keys?

2020-03-31 mysql

I have a primary key CustomerNo from a table and another primary key ProductNo. from another table. I want to use them as composite key in addition to a third column called Date in a third table called Order. So the idea is that in the Order table, I have a composite key that is CustomerNo-ProductNo-Datecombined. The Date (as an individual column) may be duplicated in the table. However, the composite key combination will remain unique. How do I make this possible?


I agree with P.Salmon that it seems your table structure is not ideal. Creating a custom composite key like this would hurt your search performance because it would not be a quick lookup of indexed keys (such as you might search for "all orders placed yesterday," "all orders this year by a particular customer," or even "which products is no one buying." For each of those searches you would have to parse out the proper information from the composite key, meaning not only would your SQL query return every result, but you'd also have to parse each result and determine whether item with key 0015053020200412124523 is part of customer 0530's order.

One alternative solution would be an Orders table that has individual references to CustomerNo and ProductNo, plus a field for date. You'd also want an autoincrement primary key on the table, then you could leverage the strength of SQL and indexes (indices?) for any queries.

MySQL doesn't directly support composite keys like some other database systems do, but in this case I don't think that's going to hurt you. At least, you haven't explained a reason why your system needs a composite key over foreign key relationships. You could fake a composite key by using a stored procedure to trigger the insert, or several other methods, but again I think that's going to really cause your database to be difficult to work with in the future and encourage you to re-think your database design.