Adding a forging key to tables in Netezza / PureData is a best practice; especially, when working with dimensionally modeled data warehouse structures and with modern governance, integration (including virtualization), presentation semantics (including reporting, business intelligence and analytics).
Foreign Key (FK) Guidelines
- A primary key must be defined on the table and fields (or fields) to which you intend to link the foreign key
- Avoid using distribution keys as foreign keys
- Foreign Key field should not be nullable
- Your foreign key link field(s) must be of the same format(s) (e.g. integer to integer, etc.)
- Apply standard naming conventions to constraint name:
- FK_<<Constraint_Name>>_<<Number>>
- <<Constraint_Name>>_FK<<Number>>
- Please note that foreign key constraints are not enforced in Netezza
Steps to add a Foreign Key
The process for adding foreign keys involves just a few steps:
- Verify guidelines above
- Alter table add constraint SQL command
- Run statistics, which is optional, but strongly recommended
Basic Foreign Key SQL Command Structure
Here is the basic syntax for adding Foreign key:
ALTER TABLE <<Owner>>.<<NAME_OF_TABLE_BEING_ALTERED>>
ADD CONSTRAINT <<Constraint_Name>>_fk<Number>>
FOREIGN KEY (<<Field_Name or Field_Name List>>) REFERENCES <<Owner>>.<<target_FK_Table_Name>.(<<Field_Name or Field_Name List>>) <<On Update | On Delete>> action;
Example Foreign Key SQL Command
This is a simple one field example of the foreign key (FK)
ALTER TABLE Blog.job_stage_fact
ADD CONSTRAINT job_stage_fact_host_dim_fk1
FOREIGN KEY (hostid) REFERENCES Blog.host_dim(hostid) ON DELETE cascade ON UPDATE no action;
Related References
Alter Table
PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL command reference, Alter Table, constraints
- Data Modeling – Fact Table Effective Practices
- Data Modeling – Dimension Table Effective Practices
- Data Warehouse – Effective Practices
- Database Table Field Ordering Effective Practices
- Database – What is a Composite Primary Key
- Database – What is a Primary Key?
- Netezza/ PureData – how to add a primary key
- PureData / Netezza – Useful links
One thought on “Netezza / PureData – How to add a Foreign Key”