Search code examples
sqloracle-databaseprimary-keyoracle12cmaximo

Does the ASSET table in Maximo have a primary key?

I'm trying to determine if the asset table in Maximo 7.6.1.1 has a primary key (query source):

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'ASSET'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

[0 records returned]

I'm surprised that the query did not find a primary key in this table.

Does the ASSET table in Maximo have a primary key? Or is there a problem with the query?

Edit:

Now that I look at the table properties in Toad, I see that it clearly states that there is no primary key on the asset table:

enter image description here

Solution

  • Maximo doesn't use database PK feature but instead will use different unique indices and an applicative composite PK that you can find by looking at the MAXATTRIBUTE.PRIMARYKEYCOLSEQ field.

    Example SQL for ASSET, the applicative primary key would be SITEID, ASSETNUM:

    select ATTRIBUTENAME,PRIMARYKEYCOLSEQ from maxattribute where primarykeycolseq is not null and objectname='ASSET' ORDER BY PRIMARYKEYCOLSEQ;

    You'll also find for every persistent object a unique index bound to a single BIGINT attribute that is populated by a sequence object in Oracle. The corresponding attribute is usually the table name followed by "ID".

    Example: With WORKORDER, that attribute is WORKORDERID. You can find the index with such a query:

    select * from maxsysindexes where tbname='WORKORDER' and required=1;

    And you can identify the attribute by looking at the index found with a tool such as Oracle SQL Dev or with a query like this:

    SELECT COLNAME FROM MAXSYSKEYS WHERE IXNAME=(select NAME from maxsysindexes where tbname='WORKORDER' and required=1);