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
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
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.
- How can I temporarily disable a foreign key constraint in MySQL?
- How can I prevent SQL injection in PHP?
- Insert auto increment primary key to existing table
- How to truncate a foreign key constrained table?
- Using Composite Primary Key As Foreign Key
- mySQL Composite Key, Primary Key, Unique Key and References to the composite key
- ALTER TABLE to add a composite primary key
- how to make a composite foreign key (not composite primary key) as unique in mysql
- How to properly create composite primary keys - MYSQL
- How to make a composite key to be unique?