PostgresQL Foreign Key Syntax Error

Question

When attempting to create the second table in this respective database, I'm getting the following error message:

ERROR:  syntax error at or near "REFERENCES"
LINE 3: master_directory REFERENCES auth_table (directory),

Here's the database structure that I attempted to create:

CREATE TABLE auth_table (
id SERIAL PRIMARY KEY,
directory VARCHAR,
image VARCHAR
)

CREATE TABLE master_table (
id SERIAL PRIMARY KEY,
master_directory references auth_table (directory),
master_image references auth_table (image)
)

Any reason why I'm receiving that error? Any help would be appreciated!

The column should have a data type (before references) and varchar keys are generally not a good idea. Plus there should be a ; after the ) of your definitions. — joop
– joop, Commented Jul 4, 2017 at 17:09
@joop forgot to include the ; in my definition, my fault. and i didn't know that the datatype before references was necessary! i kinda figured that the reference always takes the same data type as the original. many thanks though! — Benji Durden
– Benji Durden, Commented Jul 4, 2017 at 17:19

Mike Sherrill 'Cat Recall' · Accepted Answer · 2017-07-05 16:30:15Z

2

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

answered Jul 5, 2017 at 16:30

Mike Sherrill 'Cat Recall'

96.6k20 gold badges132 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Benji Durden Over a year ago

to reference a foreign key - does that value have to be unique? like what if the value stays the same throughout both tables?

Mike Sherrill 'Cat Recall' Over a year ago

A foreign key constraint has to refer to one or more columns that have some kind of unique constraint on them--either unique, not null unique, or primary key.

Collectives™ on Stack Overflow

PostgresQL Foreign Key Syntax Error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related