Custom Primary Key

[#1]

Hi,

We would like to specify our own Primary key for a database schema, without Adobe campaign creating its own PK in the database.

How can I do this?

[#2 · Accepted solution]

Hi emilr,

As Daniel wrote it correctly, the behavior is by design in Adobe Campaign, due to the SQL functions used for doing the mapping between AC schemas and the underlying RDBMS engines, that you can read in the Administration>Settings>SQL scripts node.

For instance for SQL Server, AC never generates true primary key with autoincrement sequence nor cluster index for the primary key.
The only way to do this, with the default factory SQL script for mapping (and I advise to not change them to keep Adobe support/guaranty), is to create the primary key/autoincrement/cluster index at SQL Server side.

So, either use Visual Studio UI or command line for the SQL script create table/alter table; if you don't have access to the SQL Server server itself, you can do it through Adobe Campaign client, either by using a workflow with SQL code activity, or by the menu Tools/Advanced/Update database structure, then select the table to alter, then it generates a sql code and you can modify it to force the cluster index/autoincrement then run it.

... or ask it to your DBA

Regards
J-Serge

[#3]

Hi,

Your code looks correct to me. I have tested and works. On the database layer; OOTB schemas have the same pattern. In adobe campaign here is am example of the Tracking Logs schema where TrackingLogId is the pkey in the schema:

You can see it has a red key next to it to indicate primary key.

Whereas if I look in the underlying database - it see this:

So what you are experiencing is by design.

I hope that helps clear that up.

Kind Regards,

Dan Lewis

Tech Marketer Solutions

http://www.techmarketersolutions.com

[#4]

And the SQL generated:

CREATE TABLE ElsBlackListPureEmailTest(
  iBlackListPureEmail tinyint NOT NULL Default 0, 
  sBrand nvarchar(50), 
  sCountry nvarchar(2), 
  sEmail nvarchar(128), 
  tsCreated datetimeoffset(6)
);
CREATE UNIQUE INDEX ElsBlackListPureEmailTest_id ON ElsBlackListPureEmail
Test(sEmail, sBrand);

[#5]

<element label="Black List Pure Email Test" name="blackListPureEmailTest">
  <key internal="true" name="id">
      <keyfield xpath="@email"/>
      <keyfield xpath="@brand"/>
    </key>
    <attribute label="Brand" length="50" name="brand" type="string"/>
    <attribute label="Country" length="2" name="country" type="string"/>
    <attribute label="Email" length="128" name="email" type="string"/>
    <attribute label="No longer pure email" length="128" name="blackListPureEmail"
               type="boolean"/>
    <attribute label="Created date" name="created" type="datetime"/>
  </element>

[#6]

Can you please share your schema xml?

[#7]

Hi,

I test your approatch and it creates a UNIQE INDEX in the SQL Server but I need it to be a PRIMARY KEY on the database server as well.

[#8]

Hi,

This is achievable by adding several additional lines of code into your xml schema.

Firstly, you need to advise the schema that that primary key will not be auto generated.

If you look at an out of the box schema (such as nms:recipient) you will notice this line of xml:

<element autopk="true"

In order to use your own primary key, you can add:

<element autopk="false"

So for example in my custom schema that I can create with my own primary key being generated: my line of code would look like this:

<element autopk="false" label="PK Demo" name="pkDemo">

Then, I just need to tell the schema which attribute would now become the primary key. So this is the attribute that i will use as my PK:

   <attribute label="This is my Primary Key" name="id" type="long"/>

So i add the following xml :

    <key internal="true" name="id">

      <keyfield xpath="@id"/>

    </key>

This should now result in the following:

If you need any further assistance let me know.

Dan Lewis

Tech Marketer Solutions

http://www.techmarketersolutions.com