While primary keys (PK) are not enforced within Netezza, they still provide significant value and should be added. Among the values that adding primary key provides are:
- Inform tools, which have meta Data import capabilities; for example, Aginity, ETL tools, data modeling tools, Infosphere Data Architect, DataStage and DataQuality, Infosphere Information Server suite of tools (e.g. Governance Console, Information analyzer, etc.).
- Visually helps developers, data modelers, and users to know what the keys primary keys of the table are, which may not be obvious with the table structure. This is especially true for table utilizing compound keys as the primary key.
- The query optimizer will use these definitions define efficient query execution plans
- Identifying Primary Keys information provides migratable self-documenting Meta Data
- Aides in the facilitation of future data and application enrichment projects
Basic Primary Key syntax
ALTER TABLE <<schema.tablename>> ADD CONSTRAINT <<ConstraintName>> PRIMARY KEY (FieldNames);
Example Primary Key syntax
ALTER TABLEtime_dim ADD CONSTRAINT time_dim_pk PRIMARY KEY (time_srky);
- PureData – Table Effective Practices
- Database Table Field Ordering Effective Practices
- Netezza / PureData – How to add comments on a table