Usage
dimension: field_name {
primary_key: yes
}
}
Hierarchy
primary_key |
Possible Field Types
DimensionAccepts
A Boolean (yes or no) |
Definition
The primary_key
parameter specifies that a dimension is the primary key for its view. The default value is false
. For example:
dimension: id {
primary_key: yes
sql: ${TABLE}.id ;;
}
The primary_key
parameter has the following qualities and requirements:
- Helps Looker to avoid unnecessary
GROUP BY
clauses. - Is necessary to make the
foreign_key
parameter work properly. - Is required for the proper functioning of symmetric aggregates. When you join views with measures (aggregates), a
primary_key
must be defined on each side of the join to properly calculate the aggregations. - Cannot be a
dimension_group
that specifies more than onetimeframe
.
Creating a compound primary key
If you have a primary key that is comprised of multiple columns, you can create a dimension that concatenates those columns, then declare that as your primary key. For example:
dimension: primary_key {
primary_key: yes
sql: CONCAT(${TABLE}.promotion_id, ${TABLE}.sku) ;;
}
CONCAT
works in some databases, such as MySQL, but other databases might have a different equivalent. Redshift, for example, uses||
as its concatenation operator.
Using a persistent derived table (PDT) to create a primary key
In cases where there is no reasonable way to identify a unique row as a primary key, you can create a persistent derived table (PDT) to generate a unique ID. For example, the following derived table uses the table's row number to serve as a primary key:
view: transaction {
# sql_table_name: warehouse.transaction
derived_table: {
sql: SELECT ROW_NUMBER as transaction_pk, * FROM warehouse.transaction ;;
sql_trigger_value: SELECT CURRENT_DATE ;;
indexes: [customer_id, product_id, salesperson_id, transaction_date]
}
dimension: transaction_pk {
type: number
primary_key: yes
hidden: yes
sql: ${TABLE}.transaction_pk ;;
...
}
}