Add a primary or foreign key to a table column.
Usage
dbAddKey(
conn,
name,
colname,
type = c("primary", "foreign"),
reference,
colref,
display = TRUE,
exec = TRUE
)
Arguments
- conn
A connection object.
- name
A character string, or a character vector, specifying a PostgreSQL table name.
- colname
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.
- type
The type of the key, either
"primary"
or"foreign"
- reference
A character string specifying a foreign table name to which the foreign key will be associated (ignored if
type == "primary"
).- colref
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"
).- display
Logical. Whether to display the query (defaults to
TRUE
).- exec
Logical. Whether to execute the query (defaults to
TRUE
).
See also
The PostgreSQL documentation: http://www.postgresql.org/docs/current/static/sql-altertable.html
Author
Mathieu Basille mathieu@basille.org
Examples
## 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");