References without parenthesis-CodePudding

I don' understand the difference between :

CREATE TABLE country(
   name VARCHAR,
   capital VARCHAR
);
CREATE TABLE people(
   name VARCHAR,
   grade INTEGER ,
   country VARCHAR,
   PRIMARY KEY (name, country),
   FOREIGN KEY country REFERENCES country(name)
);

and


CREATE TABLE country(
   name VARCHAR PRIMARY KEY,
   capital VARCHAR
);

CREATE TABLE people(
   name VARCHAR,
   grade INTEGER ,
   country VARCHAR,
   PRIMARY KEY (name, country),
   FOREIGN KEY country REFERENCES country
);

In particular, I'm unsure how the second one is interpreted.

CodePudding user response：

Omitting the column name(s) from the foreign key constraint means that the constraint will refer to the primary key of the targeted table. From the docs

You can also shorten the above command to:
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);
because in absence of a column list the primary key of the referenced table is used as the referenced column(s).

and in the CREATE TABLE docs:

REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (column constraint)
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (table constraint)
These clauses specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column(s) of some row of the referenced table. If the refcolumn list is omitted, the primary key of the reftable is used. The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.

CodePudding user response：

In postgres for create foreign keys you should like FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] format

For see more about create table read document: https://www.postgresql.org/docs/9.6/sql-createtable.html

Sample 1:

CREATE TABLE country
(
    name    VARCHAR PRIMARY KEY,
    capital VARCHAR
);

CREATE TABLE people
(
    name    VARCHAR,
    grade   INTEGER,
    country VARCHAR,
    PRIMARY KEY (name, country),
    FOREIGN KEY (country) REFERENCES country (name)
);

Sample 2:

CREATE TABLE country
(
    name    VARCHAR PRIMARY KEY,
    capital VARCHAR
);

CREATE TABLE people
(
    name    VARCHAR,
    grade   INTEGER,
    country VARCHAR REFERENCES country (name),
    PRIMARY KEY (name, country)
);