Add key.

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).

Value

If exec = TRUE, returns (invisibly) TRUE if the key was successfully added.

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)
#> ℹ ALTER TABLE "sch1"."tbl1" ADD PRIMARY KEY ("id1");
#> ✖ Query not executed

## Primary key using multiple columns
dbAddKey(conn, name = c("sch1", "tbl1"), colname = c("id1", "id2",
    "id3"), exec = FALSE)
#> ℹ ALTER TABLE "sch1"."tbl1" ADD PRIMARY KEY ("id1", "id2", "id3");
#> ✖ Query not executed

## Foreign key
dbAddKey(conn, name = c("sch1", "tbl1"), colname = "id", type = "foreign",
    reference = c("sch2", "tbl2"), colref = "id", exec = FALSE)
#> ℹ ALTER TABLE "sch1"."tbl1" ADD FOREIGN KEY ("id") REFERENCES "sch2"."tbl2" ("id");
#> ✖ Query not executed

## 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)
#> ℹ ALTER TABLE "sch1"."tbl1" ADD FOREIGN KEY ("id1", "id2") REFERENCES "sch2"."tbl2" ("id3", "id4");
#> ✖ Query not executed