Add a primary or foreign key to a table column.
dbAddKey(
conn,
name,
colname,
type = c("primary", "foreign"),
reference,
colref,
display = TRUE,
exec = TRUE
)
A connection object.
A character string, or a character vector, specifying a PostgreSQL table name.
A character string specifying the name of the column to which the key will be assign; alternatively, a character vector specifying the name of the columns for keys spanning more than one column.
The type of the key, either "primary"
or
"foreign"
A character string specifying a foreign table
name to which the foreign key will be associated (ignored if
type == "primary"
).
A character string specifying the name of the
primary key in the foreign table to which the foreign key will
be associated; alternatively, a character vector specifying
the name of the columns for keys spanning more than one column
(ignored if type == "primary"
).
Logical. Whether to display the query (defaults to
TRUE
).
Logical. Whether to execute the query (defaults to
TRUE
).
If exec = TRUE
, returns TRUE
if the key was successfully added.
The PostgreSQL documentation: http://www.postgresql.org/docs/current/static/sql-altertable.html
## Examples use a dummy connection from DBI package
conn <- DBI::ANSI()
## Primary key
dbAddKey(conn, name = c("sch1", "tbl1"), colname = "id1", exec = FALSE)
#> Query not executed:
#> ALTER TABLE "sch1"."tbl1" ADD PRIMARY KEY ("id1");
## Primary key using multiple columns
dbAddKey(conn, name = c("sch1", "tbl1"), colname = c("id1", "id2",
"id3"), exec = FALSE)
#> Query not executed:
#> ALTER TABLE "sch1"."tbl1" ADD PRIMARY KEY ("id1", "id2", "id3");
## Foreign key
dbAddKey(conn, name = c("sch1", "tbl1"), colname = "id", type = "foreign",
reference = c("sch2", "tbl2"), colref = "id", exec = FALSE)
#> Query not executed:
#> ALTER TABLE "sch1"."tbl1" ADD FOREIGN KEY ("id") REFERENCES "sch2"."tbl2" ("id");
## Foreign key using multiple columns
dbAddKey(conn, name = c("sch1", "tbl1"), colname = c("id1", "id2"),
type = "foreign", reference = c("sch2", "tbl2"), colref = c("id3",
"id4"), exec = FALSE)
#> Query not executed:
#> ALTER TABLE "sch1"."tbl1" ADD FOREIGN KEY ("id1", "id2") REFERENCES "sch2"."tbl2" ("id3", "id4");