Menu
I'm trying to learn how to use keys and to break the habit of necessarily having SERIAL type IDs for all rows in all my tables. At the same time, I'm also doing many-to-many relationships, and so requiring unique values on either column of the tables that coordinate the relationships would hamper that.
How can I define a primary key on a table such that any given value can be repeated in any column, so long as the combination of values across all columns is never repeated exactly?
350
How can we set PRIMARY KEY on multiple columns of an existing MySQL table? We can set PRIMARY KEY constraint on multiple columns of an existing table by using ADD keyword along with ALTER TABLE statement.
A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
We create composite primary keys to guarantee the uniqueness column values which compose a single record. It is a constraint which helps prevent insertion of data which should not be duplicated.
Quoted from the CREATE TABLE Syntax page:
A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.
Something like this can be used for multi-column primary keys:
CREATE TABLE product ( category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id) ); From 13.1.20.6 FOREIGN KEY Constraints
91
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
