Setting existing field as Primary Key on Marketing Cloud Data Extension

Question

We have a data extension which doesn't have any primary key set. Can't we back it up, clear all records and then set an existing field(EmailAddress field we have) as the primary key?

The reason behind why not a new DE is, the existing one is being used for an integration so without impacting the external integration configuration we can proceed if we can updated the existing DE.

I've tried this approach with a test DE and came to know even after clearing the records, primary key checkbox is disabled for all the fields. So before proceeding wanted to confirm the behavior.

Answer 1

• I have a feeling that altering the primary key will change your existing integration no matter what and you should be prepared for this.

• In a data extension with data already inside, enforcing a primary key should not work, a limit like this endangers records that were previously ok. This can create decisions that the system cannot make on the fly.

My suggested approach in likely 90% of cases:

pre-cutover:

• Determine which is relevant for the current integration: name or customerkey. This field will now be considered [currentName].

• Create a copy of the DE, give the copy the intended, new structure. Keep the existing one with the legacy structure.

• If you want, migrate your existing data over from old to new now.

This will also show you if your new configuration is compatible with how the data has come in so far.

for cutover:

• Rename the existing, wrong one to "[currentName]_deprecated" or similar, freeing the name used in integration.

• Rename the new one to "[currentName]", closing the gap.

This should take around 5 seconds of downtime.

If that is too long, write a script that renames the relevant fields in succession, and you can get it to within a second of downtime. If that is too long, I don't think I can help :)

The benefits of this:

• You can revert and switch back to the old one within 5 seconds if you notice the integration does break due to the configuration change.
• You can do migration of existing data before or after the changeover.

Limitations:

• If your existing integration were to use the actual immutable (and on the UI, invisible) ObjectId, which is most likely does not - it doesn't work without an integration change.

gotchas:

• if you target the DE in question with an SQL query, those display the name on the UI, but do use the customerkey for target DEs. Make sure to refresh the query after the cahnge. Selecting FROM uses the name, and this will automatically adapt.