Postgresql Foreign Key Syntax

PostgreSQL Foreign Key syntax

Assuming this table:

CREATE TABLE students 
( 
  student_id SERIAL PRIMARY KEY,
  player_name TEXT
);

There are four different ways to define a foreign key (when dealing with a single column PK) and they all lead to the same foreign key constraint:

1. Inline without mentioning the target column:

   CREATE TABLE tests 
   ( 
      subject_id SERIAL,
      subject_name text,
      highestStudent_id integer REFERENCES students
   );
   

2. Inline with mentioning the target column:

   CREATE TABLE tests 
   ( 
      subject_id SERIAL,
      subject_name text,
      highestStudent_id integer REFERENCES students (student_id)
   );
   

3. Out of line inside the create table:

   CREATE TABLE tests 
   ( 
     subject_id SERIAL,
     subject_name text,
     highestStudent_id integer, 
     constraint fk_tests_students
        foreign key (highestStudent_id) 
        REFERENCES students (student_id)
   );
   

4. As a separate alter table statement:

   CREATE TABLE tests 
   ( 
     subject_id SERIAL,
     subject_name text,
     highestStudent_id integer
   );
   
   alter table tests 
       add constraint fk_tests_students
       foreign key (highestStudent_id) 
       REFERENCES students (student_id);
   

Which one you prefer is a matter of taste. But you should be consistent in your scripts. The last two statements are the only option if you have foreign keys referencing a PK that consists of more than one column - you can't define the FK "inline" in that case, e.g. foreign key (a,b) references foo (x,y)

Only version 3) and 4) will give you the ability to define your own name for the FK constraint if you don't like the system generated ones from Postgres.

---

The serial data type is not really a data type. It's just a short hand notation that defines a default value for the column taken from a sequence. So any column referencing a column defined as serial must be defined using the appropriate base type integer (or bigint for bigserial columns)

How to add foreign key in PostgreSQL

Name column in author table is primary key and it's referenced as foreign key in bookstore table.

-- PostgreSQL (v11)

create table author (name varchar primary key, place varchar);

create table bookstore (book_name varchar primary key, author varchar, price decimal
, CONSTRAINT fk_author_bookstore
      FOREIGN KEY(author) 
      REFERENCES author(name));

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=8394f796433ed8bc170c2889286b3fc2

Add foreign key after table creation

-- PostgreSQL(v11)
ALTER TABLE bookstore
      ADD CONSTRAINT fk_author_bookstore FOREIGN KEY (author) 
          REFERENCES author (name);

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=d93cf071bfd0e3940dfd256861be813c

PostgreSQL: SQL script to get a list of all foreign key references to a table

You have to JOIN pg_constraint to pg_attribute and un nest the column arrays (could be compound keys) to get the referenced column name in the base table.
You have to use pg_class to get the table names.
pg_get_constraintdef gives you the actual SQL line that is used to create the constraint.

SELECT (select  r.relname from pg_class r where r.oid = c.confrelid) as base_table,
       a.attname as base_col,
       (select r.relname from pg_class r where r.oid = c.conrelid) as referencing_table,
       UNNEST((select array_agg(attname) from pg_attribute where attrelid = c.conrelid and array[attnum] <@ c.conkey)) as referencing_col,
       pg_get_constraintdef(c.oid) contraint_sql
  FROM pg_constraint c join pg_attribute a on c.confrelid=a.attrelid and a.attnum = ANY(confkey)
 WHERE c.confrelid = (select oid from pg_class where relname = 'breeds')
   AND c.confrelid!=c.conrelid;

PostgresQL Foreign Key Syntax Error

You've left the data type off, but that syntax error is the least of your problems.

Your foreign key references need to refer to unique column(s). So "auth_table" probably needs to be declared one of these ways. (And you probably want the second one, if your table has something to do with the paths to files.)

CREATE TABLE auth_table (
  id SERIAL PRIMARY KEY,
  directory VARCHAR not null unique,
  image VARCHAR not null unique
);

CREATE TABLE auth_table (
  id SERIAL PRIMARY KEY,
  directory VARCHAR not null,
  image VARCHAR not null,
  unique (directory, image)
);

Those unique constraints mean quite different things, and each requires a different foreign key reference. Assuming that you want to declare "auth_table" the second way, "master_table" probably ought to be declared like one of these. (Deliberately ignoring cascading updates and deletes.)

CREATE TABLE master_table (
  master_directory varchar not null,
  master_image varchar not null,
  primary key (master_directory, master_image),
  foreign key (master_directory, master_image)
    references auth_table (directory, image)
);

CREATE TABLE master_table (
  id integer primary key,
  foreign key (id) references auth_table (id)
);

Postgres: complex foreign key constraints

I figured out having a function to execute on constraint will solve this issue.
The function is_storeitem does the validation. I believe this feature can be used for even complex validations

create or replace function is_storeitem(pItemcode nchar(40), pStoreId nchar(20)) returns boolean as $$
select exists (
    select 1
    from public.storeitem si, public.item i, public.store s
    where si.itemid = i.itemid and i.itemcode = pItemcode and s.Storeid = pStoreId and s.storeid = si.storeid
);
$$ language sql;

create table SZData
(
    StoreID             NCHAR(20)              not null,
    ItemCode            NCHAR(100)             not null,
    TextData            NCHAR(20)              not null,
    constraint PK_SIDATA primary key (ItemCode, StoreID),
    foreign key (StoreID) references Store(StoreID),
    foreign key (ItemCode) references Item(ItemCode),
    CONSTRAINT ck_szdata_itemcode CHECK (is_storeitem(Itemcode,StoreID))
);

This perfectly works with postgres 9.6 or greater.

Can FOREIGN KEY be omitted in PostgreSQL when using REFERENCES?

The two samples you show do the same thing, just with a different syntax.

The first method is called table constraint, the second column constraint, but the latter name is somewhat misleading because the constraint is on the table as well.

The main difference is that the column constraint syntax is shorter, but cannot be used for all constraints: if you have for example a primary key that contains two columns, you have to write it in the table constraint syntax.

how to set constraint and foreign key on a column

There are few things missing:

• The columns you're using for the primary key on grid_cell do not exist in the table: fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON and fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON.
• The column names on grid_cell suggest you're storing json strings, so you should be using the data type jsonb instead of text.
• The foreign key columns declared on the constraint on grid_cell_particular also do not exist on the table itself. In order to create a foreign key you must map the foreign columns to existing columns on your table. You can name them as you want, e.g. fk_grid_cell_treatment and fk_grid_cell_buffer.

Unrelated:

Consider using INDENTITY columns instead of serial. Check this out: Don't use serial

CREATE TABLE IF NOT EXISTS grid_cell(
  fourCornersTreatmentAsGeoJSON jsonb,
  fourCornersBufferAsGeoJSON jsonb,
  fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON jsonb,
  fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON jsonb,
  PRIMARY KEY (fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,
               fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON)
);

CREATE TABLE IF NOT EXISTS grid_cell_particular (
  gridCellParticularPK serial PRIMARY KEY,
  isTreatment boolean,
  isBuffer boolean,
  distanceFromTreatmentToNearestEdge float8,
  distanceFromBufferToNearestEdge float8,    
  fk_grid_cell_treatment jsonb,
  fk_grid_cell_buffer jsonb,
  CONSTRAINT constrains_FK_gridCell_gridCellParticular 
    FOREIGN KEY (fk_grid_cell_treatment,fk_grid_cell_buffer)
      REFERENCES grid_cell 
       (fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,
        fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON),
  CONSTRAINT unique_fk 
    UNIQUE (fk_grid_cell_treatment,fk_grid_cell_buffer)
 );

Why is my regular foreign key constraint not deferrable?

A) The doc link is for version 9.1 which is well past EOL(~5.25 yrs).

B) Yes, you missed the next paragraph:

Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. .

So the constraint needs to be created as DEFERRABLE or altered to it. See ALTER TABLE:

ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

---

Related Topics