Amazon Redshift - Foreign Keys
Relationships are what turns a list of tables into a data model: they tell you which table a column points at, drive ER diagrams, and make joins discoverable to anyone reading the documentation. In Amazon Redshift they need extra attention, because the database stores them without enforcing them.
Constraints
Amazon Redshift documents this explicitly: "Uniqueness, primary key, and foreign key constraints are informational only; they are not enforced by Amazon Redshift when you populate a table." Only NOT NULL is enforced.
Two consequences follow:
- A declared relationship can be violated by the data. Loads succeed even when they break a foreign key, so a constraint existing in the schema is not evidence that the data is consistent.
- Constraints still matter. Redshift uses primary and foreign keys as planner hints — for join ordering, removing redundant joins, and subquery decorrelation. AWS recommends declaring them whenever they are genuinely valid, and not declaring them when their validity is in doubt.
For documentation this means the model in Redshift is often incomplete: teams that cannot guarantee validity simply omit the constraints, and the relationships live only in the ETL code.
What Dataedo imports
| Redshift constraint | Imported as |
|---|---|
| Primary key | Primary key |
| Foreign key | Relationship (PK/FK) |
| Unique constraint | Unique key |
NOT NULL | Column nullability |
| Check constraint | Not imported |
Imported keys appear in the Relationships tab of each table and are drawn automatically on ER diagrams in subject areas. Redshift has no user-defined indexes, so nothing index-related is imported.
Documenting missing relationships
When a relationship exists in the data but not in the schema, add it in Dataedo as a user-defined relationship — it behaves like an imported one in the Relationships tab, on diagrams, and in exports, and it survives re-imports.
Testing relationships
Because Redshift never validates constraints itself, testing is the only way to find out whether a relationship — imported or user-defined — actually holds in the data. Dataedo supports the Relationships (PK/FK) tester for Redshift: it runs the checks in the database and reports one of three outcomes (OK, UNKNOWN, FAIL).
Use it in two situations that are especially common with Redshift:
-
Before declaring a constraint in Redshift. AWS warns that an invalid key can make queries return incorrect results. A passing test is the evidence you need before adding the constraint to the source schema.
-
After importing existing constraints. An imported foreign key proves only that somebody declared it, not that the data respects it.
The tester needs a running Agent and edit rights on both tables, and both tables have to belong to the same data source. Testing reads the tested columns, so the account needs SELECT on them — see Required access level.
