You can create an index for composite primary key that uses the same fields present in your composite primary key. mysql> alter table new_orders ADD INDEX new_index (order_id, product_id); Hopefully, now you can create composite primary key in MySQL.
No. You cannot use more than 1 primary key in the table. for that you have composite key which is combination of multiple fields.
We will use the below query to create a Composite Key. Query: CREATE TABLE COMPO ( EMP_ID INT, DEPT_ID INT, EMPNAME VARCHAR(25), GENDER VARCHAR(6), SALARY INT --> //This statement will create a //composite Primary Key from PRIMARY KEY (EMP_ID,DEPT_ID) with the help of Column EMP_ID and DEPT_ID );
ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);
If a primary key already exists then you want to do this
ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);
@Adrian Cornish's answer is correct. However, there is another caveat to dropping an existing primary key. If that primary key is being used as a foreign key by another table you will get an error when trying to drop it. In some versions of mysql the error message there was malformed (as of 5.5.17, this error message is still
alter table parent drop column id;
ERROR 1025 (HY000): Error on rename of
'./test/#sql-a04_b' to './test/parent' (errno: 150).
If you want to drop a primary key that's being referenced by another table, you will have to drop the foreign key in that other table first. You can recreate that foreign key if you still want it after you recreate the primary key.
Also, when using composite keys, order is important. These
1) ALTER TABLE provider ADD PRIMARY KEY(person,place,thing);
and
2) ALTER TABLE provider ADD PRIMARY KEY(person,thing,place);
are not the the same thing. They both enforce uniqueness on that set of three fields, however from an indexing standpoint there is a difference. The fields are indexed from left to right. For example, consider the following queries:
A) SELECT person, place, thing FROM provider WHERE person = 'foo' AND thing = 'bar';
B) SELECT person, place, thing FROM provider WHERE person = 'foo' AND place = 'baz';
C) SELECT person, place, thing FROM provider WHERE person = 'foo' AND place = 'baz' AND thing = 'bar';
D) SELECT person, place, thing FROM provider WHERE place = 'baz' AND thing = 'bar';
B can use the primary key index in ALTER statement 1
A can use the primary key index in ALTER statement 2
C can use either index
D can't use either index
A uses the first two fields in index 2 as a partial index. A can't use index 1 because it doesn't know the intermediate place portion of the index. It might still be able to use a partial index on just person though.
D can't use either index because it doesn't know person.
See the mysql docs here for more information.
You may simply want a UNIQUE CONSTRAINT. Especially if you already have a surrogate key. (example of an already existing surrogate key would be a single column that is an AUTO_INCREMENT )
Below is the sql code for a Unique Constraint
ALTER TABLE `MyDatabase`.`Provider`
ADD CONSTRAINT CK_Per_Place_Thing_Unique UNIQUE (person,place,thing)
;
alter table table_name add primary key (col_name1, col_name2);
It`s definitely better to use COMPOSITE UNIQUE KEY, as @GranadaCoder offered, a little bit tricky example though:
ALTER IGNORE TABLE table_name ADD UNIQUES INDEX idx_name(some_id, another_id, one_more_id);
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