CODEWITHSUNDEEP
mysqldatabase
mspir
mspir

Reputation: 1734

MySQL: Creating new unique field in already populated table

I need to create a field in a database table, already populated with data. Of course just adding the field empty is not possible. I can only think of creating a new table with a new structure and copy the existing data to the new table, but I wonder if there is a n easier way.

Hint: it is a composite key, comprised of 3 other fields in the same table.

Edit: the field holds a varchar value Edit: since some of you ask, it is not possible to create a new UNIQUE field in a populated table just by using ADD UNIQUE. It duplicates the new (empty or not) value throughout all entries. Example:

ALTER TABLE 'tablename' ADD 'fieldname' VARCHAR( 64 ) NOT NULL , ADD UNIQUE ( 'fieldname' )

error: Duplicate entry '' for key 'fieldname'

Upvotes: 3

Views: 2698

Answers (5)

Daniel W.
Daniel W.

Reputation: 32260

I came accross the same problem that I had to add a unique constraint on an already populated table.

The error:

Integrity constraint violation: 1062 Duplicate entry '' for key 'url_UNIQUE'

How did I solve it? I put the id of the row into the unique field to avoid duplicates:

UPDATE content SET new_url = content_id WHERE new_url = '';

I then didn't have any duplicate rows anymore and was able to add the unique constraint.

Upvotes: 0

Sam Choukri
Sam Choukri

Reputation: 1904

Hint: it is a composite key, comprised of 3 other fields in the same table.

This sounds like a red-flag to me. You want to create a new field with a unique constraint comprised of the values of 3 other fields that already exist in the same table?

If all you want to do is to enforce that the combination of those three fields is unique, then you should just add a unique constraint on those 3 existing fields (not a new field with duplicate data).

ALTER TABLE tablename ADD UNIQUE (fieldname1, fieldname2, fieldname3);

Upvotes: 2

user610217
user610217

Reputation:

This is a possible workaround:

alter table foo add bar varchar(36) not null default '';

then add a trigger as per default value of GUID in for a column in mysql

Upvotes: 0

Sergii Kudriavtsev
Sergii Kudriavtsev

Reputation: 10487

Of course just adding the field empty is not possible.

Why?

I'd do the following:

  1. Create field by ALTER TABLE t ADD COLUMN new_column *type_definition*
  2. Update newly created field like UPDATE t SET new_column=*computation_expression*
  3. Add index by ALTER TABLE t ADD INDEX ... (or ALTER TABLE t ADD PRIMARY KEY ... if you need it to be primary).

Upvotes: 3

juergen d
juergen d

Reputation: 204746

You can create a new field with AUTO_INCREMENT option:

ALTER TABLE `your_table` ADD COLUMN `new_id` INT NOT NULL AUTO_INCREMENT

Upvotes: 0

Related Questions