Can I add Primary Key with many condition

Question

I've first table name "mustahik_perorangan" and the second name "data_mustahik"

mustahik perorangan have 4 primary key and foreign key in another table like this condition

PRIMARY KEY (`mustahik_nik`,`ins_provinces_code`,`ins_cities_code`,`ins_institution_types_code`,`ins_institution_serial_no`),
 KEY `fk_reference_6` (`ins_provinces_code`,`ins_cities_code`,`ins_institution_types_code`,`ins_institution_serial_no`),
 CONSTRAINT `FK_ins_musper` FOREIGN KEY (`ins_provinces_code`, `ins_cities_code`, `ins_institution_types_code`, `ins_institution_serial_no`) REFERENCES `baznasgo_s_organization`.`institutions` (`provinces_code`, `cities_code`, `institution_types_code`, `institution_serial_no`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and i want to add primary to table mustahik_perorangan, so mustahik perorangan have 5 primary key ?

but i can't do it because it condition..

ALTER TABLE mustahik_perorangan ADD idc INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY (`idc`);

May you know to do it ?

i bet its conflicting pk, he should drop the old one and rebuilt the pk.. i suppose. — Bagus Tesa, Dec 22 '16 at 01:29
What do you mean by having five primary keys, and does this even make sense? — Tim Biegeleisen, Dec 22 '16 at 01:29
yes, the suggest is that .. but so many foreign key there :( @BagusTesa — TARA, Dec 22 '16 at 01:30
look, if you dig deeper in SOF, you can find [some insight](http://stackoverflow.com/questions/2334792/how-to-add-new-column-to-existing-composite-primary-key) @Tara — Bagus Tesa, Dec 22 '16 at 01:32
Possible duplicate of [ALTER TABLE to add a composite primary key](http://stackoverflow.com/questions/8859353/alter-table-to-add-a-composite-primary-key) — e4c5, Dec 22 '16 at 01:41
i cant drop pk because have foreign key .. the foreign key cant drop — TARA, Dec 22 '16 at 01:43
I'd ***HIGHLY*** recommend you use a normal, numeric, auto incremented primary key and instead declare a multi-part `unique` key. Otherwise explain the problem you're trying to solve with a 5-part primary key and maybe we can find a better solution. — Schwern, Dec 22 '16 at 01:50

score 3 · Accepted Answer · answered Dec 22 '16 at 02:02

A table can have at most one primary key constraint.

The primary key constraint can contain multiple columns. We refer to that as a composite key.

It is possible to add a new column.

It's also possible to add a UNIQUE constraint on the column and specify AUTO_INCREMENT attribute on the column.

As an example:

  ALTER TABLE mustahik_perorangan
    ADD idc INT UNSIGNED NOT NULL AUTO_INCREMENT
  , ADD UNIQUE KEY (`idc`)

It's also possible to a sixth column to the existing composite primary key. But I don't think this is what you really want.

As a demonstration of how to add a column to an existing composite primary key, I'll provide an example.

Note that the primary key must be dropped and re-added. And a UNIQUE key must be added for the AUTO_INCREMENT column.

Assuming there are no foreign keys referencing the primary key of this table.

  ALTER TABLE mustahik_perorangan
    DROP PRIMARY KEY
  , ADD idc INT UNSIGNED NOT NULL AUTO_INCREMENT
  , ADD UNIQUE KEY (`idc`)
  , ADD PRIMARY KEY
    (`mustahik_nik`
    ,`ins_provinces_code`
    ,`ins_cities_code`
    ,`ins_institution_types_code`
    ,`ins_institution_serial_no`
    ,`idc`
    )

If there are foreign keys referencing the table, the change is a little more involved. (Did you want an additional column added to the foreign keys in the referencing tables?)

It's not entirely clear what you are attempting to achieve.

I just know unique can be auto increment .. the important is .. i want to add id with auto increment to using SELECT LAST_INSERT_ID() .. thank you so much @spencer7593 — TARA, Dec 22 '16 at 02:46

score 0 · Answer 2 · answered Dec 22 '16 at 02:12

0

Yes, you can add the primary key, but in your case, you need to drop pk and then add new pk, otherwise the engine interpretes as you are trying to add multiple pks, the multiple pks are different from composite pk, you can add composite pk, but you can't add multiple pks

alter table xx drop primary key, add primary key(k1, k2, k3);

answered Dec 22 '16 at 02:12

Kiran Kumar

1,033
7
20

yes thank you @kiran kumar .. but if i drop ..I had a long way to do .. because foreign key so many – TARA Dec 22 '16 at 02:49

Can I add Primary Key with many condition

2 Answers2