1

I have a database with the following two columns:

`MainTable`
barcode (PK)
name

I then have another table that FKs to it:

`SubTable`
barcode (FK)
info

How would I add an auto-incrementing Primary Key to the MainTable field, while ensuring barcode uniqueness? Essentially, how would I do the following without a FK error arising?

alter table maintable drop primary key;
alter table maintable add unique key (barcode);
ALTER TABLE `maintable` ADD `id` INT UNSIGNED  NOT NULL  AUTO_INCREMENT  PRIMARY KEY;
David542
  • 104,438
  • 178
  • 489
  • 842
  • Wait.... Why would you want to do this? What's the benefit to adding the auto int and replacing the natural key with it? – Zane Feb 20 '15 at 21:27
  • Sound really weird. But SQL looks like the correct approach on first view. Only one part is missing: Update of old data so entry 10000445 is not getting #1 – frlan Feb 20 '15 at 21:28
  • If I am understanding you correctly you want to add a unique constraint to your barcode column on your main table? If so this is a duplicate question answered here: http://stackoverflow.com/questions/469471/how-do-i-alter-a-postgresql-table-and-make-a-column-unique – theDarse Feb 20 '15 at 21:29
  • @theDarse no -- barcode **already** is a PK. – David542 Feb 20 '15 at 21:30
  • @Zane -- the database and needs have changed, so the barcode is changed from an int(11) to a very-long string, and isn't the best for joining on it. We don't have control of what the barcode contains. – David542 Feb 20 '15 at 21:31
  • 1
    Also the mere fact that the barcodes CAN change indicates a need for a surrogate key. – JNK Feb 20 '15 at 21:36

1 Answers1

1
ALTER TABLE mainTable DROP CONSTRAINT Primary_key
ALTER TABLE maintable ADD CONSTRAINT constraint_name UNIQUE (barcode)
ALTER TABLE maintable ADD COLUMN Id INT PRIMARY KEY AUTO_INCREMENT

This would add your new primary key, then you would need to add a new column to the sub table referencing the new primary key as a FK, and drop the old Fk out out the sub table

theDarse
  • 749
  • 5
  • 13