Add foreign keysSource:
dm_add_fk() marks the specified
columns as the foreign key of table
respect to a key of table
Usually the referenced columns are a primary key in
However, it is also possible to specify other columns via the
check == TRUE, then it will first check if the values in
columns are a subset
of the values of the key in table
dm_add_fk( dm, table, columns, ref_table, ref_columns = NULL, ..., check = FALSE, on_delete = c("no_action", "cascade") )
A table in the
The columns of
tablewhich are to become the foreign key columns that reference
ref_table. To define a compound key, use
The table which
tablewill be referencing.
The column(s) of
tablewhich are to become the referenced column(s) in
ref_table. By default, the primary key is used. To define a compound key, use
These dots are for future extensions and must be empty.
TRUE, a check will be performed to determine if the values of
columnsare a subset of the values of the key column(s) of
Defines behavior if a row in the parent table is deleted. -
"no_action", the default, means that no action is taken and the operation is aborted if child rows exist -
"cascade"means that the child row is also deleted This setting is picked up by
set_key_constraints = TRUE, and by
dm_sql(), and might be considered by
dm_rows_delete()in a future version.
It is possible that a foreign key (FK) is pointing to columns that are neither primary (PK) nor explicit unique keys (UK). This can happen
when a FK is added without a corresponding PK or UK being present in the parent table
These columns are then a so-called "implicit unique key" of the referenced table and can be listed via
nycflights_dm <- dm( planes = nycflights13::planes, flights = nycflights13::flights, weather = nycflights13::weather ) nycflights_dm %>% dm_draw() # Create foreign keys: nycflights_dm %>% dm_add_pk(planes, tailnum) %>% dm_add_fk(flights, tailnum, planes) %>% dm_add_pk(weather, c(origin, time_hour)) %>% dm_add_fk(flights, c(origin, time_hour), weather) %>% dm_draw() # Keys can be checked during creation: try( nycflights_dm %>% dm_add_pk(planes, tailnum) %>% dm_add_fk(flights, tailnum, planes, check = TRUE) ) #> Error in abort_not_subset_of(table_name, col_name, ref_table_name, ref_col_name) : #> Column (`tailnum`) of table `flights` contains values (see examples above) that are not present in column (`tailnum`) of table `planes`.