0

I have a legacy database, and several tables need a new column added that will act as the primary key for the table. The trick is there are several tens of thousands of existing rows, and the data for the new column will be the concatenation of two field values within the table.

Specifically, the new column can be temporarily created via 

SELECT *, CONCAT(ContractNum, '-', PlanId) AS ConPlanId FROM ContractPlans

Using a batch script, how can I populate the exising column fields which are currently null?

Improve this question
2
  • What fields could be null ? ContractNum and PlanId ?
    – Max
    Jun 14, 2013 at 22:01
  • @Max, none of the fields can be null, as they are both foreign keys to other tables. You and Kali gave good solutions.
    – Jason
    Jun 14, 2013 at 22:07

2 Answers 2

Reset to default
3

primary key...will be the concatenation of two field values within the table.

no NO NO!!!!!

This breaks all the rules of database normalization (well, rule 1 and rule 2 actually) and it's totally unnecessary! It uses more storage, makes your queries less efficient, and may cause bugs in your processing.

Use the existing columns as the primary key.

Presumably it doesn't have a primary key declared already.

Check that you've got no duplicates in the table:

SELECT ContractNum, PlanId, COUNT(*) 
FROM ContractPlans
GROUP BY ContractNum, PlanId
HAVING COUNT(*)>1;

Fix the data if you need to, then

ALTER TABLE ContractPlans
ADD PRIMARY KEY (ContractNum, PlanId);
Improve this answer
5
  • Normally, I would agree with you. However, the concatenation is the result of two foreign key fields. The table is modeling a many to many aggregate object in Django and contains 3 foreign key references in addition to its own fields. However, those existing fields are not suitable for indexing for a variety of reasons.
    – Jason
    Jun 14, 2013 at 22:26
  • 1
    I can't begin to imagine why these fields "are not suitable for indexing". The fact the table holds 3 other foreign keys provides even more reason that concatenating the data to create a PK is a very bad idea. If there's some reason that normal database design does not aply here, then I'm dying to know what it is.
    – symcbean
    Jun 14, 2013 at 22:58
  • @Kaii I prefered the original version ;)
    – RandomSeed
    Jun 15, 2013 at 0:37
  • 2
    @symcbean, this is our way of working around the Django framework limitation of no multiple primary keys for a single model. I agree its not the best, but as the multiple primary key request was opened over 4 years ago on the Django project site and has not been resolved forces some creative and at times less than optimal solutions.
    – Jason
    Jun 15, 2013 at 0:50
  • 1
    Haven't done much django programming, but isn't this what meta.unique_together is intended to solve? docs.djangoproject.com/en/dev/ref/models/options/…
    – symcbean
    Jun 15, 2013 at 23:19
1

If one of your ContractNum or PlanId can be null a concat will yield a null value. Make a backup of your table first but this should do the trick : 

UPDATE ContractPlans 
   SET ConPlanId = CONCAT(COALESCE(ContractNum, ''), '-', COALESCE(PlanId, ''))
   WHERE COALESCE(ConPlanId, '') = ""

EDIT: As Kaii noted, if your ConplanId field is already set, be sure to add the where clause to not overwrite it

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.