Adding a Primary Key to an Existing Database in Advantage

Question

What is the syntax to add a Primary Key to an existing Production database? I have inherited a database with no Primary Keys set for anything, and I need a primary key on each table to use my ORM for an application.

I tried googling to no avail, Advantage's documentation isn't the best in the world.

It does seem to support some of the `alter table add primary key` syntax: http://devzone.advantagedatabase.com/dz/webhelp/Advantage11/index.html — wildplasser, Jun 02 '13 at 17:42
Using `ALTER TABLE table ADD PRIMARY KEY (column)` throws me an error: Expected lexical element not found: You are missing the column names. — PRNDL Development Studios, Jun 02 '13 at 17:45
Maybe it wants a name for the constraint (this is often optional) — wildplasser, Jun 02 '13 at 17:47
`ALTER TABLE table ADD CONSTRAINT p_key PRIMARY KEY (column)` gives me the same error. :/ — PRNDL Development Studios, Jun 02 '13 at 17:49
`Advantage's documentation isn't the best in the world.` Understatement of the century, those guys are utterly useless and I hope they go out of business soon so I don't have to support it anymore. — MrLore, May 03 '17 at 13:24

Jens Mühlenhoff · Accepted Answer · 2013-06-03T12:15:53.293

Tables in free connections don't support a primary key. (They do support unique indexes however.)

Tables in dictionaries support primary keys by the table property TABLE_PRIMARY_KEY.

http://devzone.advantagedatabase.com/dz/webhelp/Advantage11.1/master_sp_modifytableproperty.htm

EXECUTE PROCEDURE sp_ModifyTableProperty(
    'TableName'
  , 'TABLE_PRIMARY_KEY'
  , 'IndexName'
  , 'RETURN_ERROR'
  , NULL
)

You have to add a unique index using the CREATE UNIQUE INDEX statement first.

CREATE UNIQUE INDEX IndexName ON TableName (ColumnName)

http://devzone.advantagedatabase.com/dz/webhelp/Advantage11.1/master_create_index.htm

Adding a Primary Key to an Existing Database in Advantage

1 Answers1