PostgreSQL: Column Reference in Foreign Key Constraint Does Not Exist

Question

I'm still new to postgreSQL. I have two tables I created that I want to go back and add Primary and Foreign key constraints for and no matter what I do I can't seem to add a foreign key. Here's what I have:

Two Tables:

test=# \d statename
      Table "public.statename"
 Column |     Type      | Modifiers
--------+---------------+-----------
 code   | character(2)  | not null
 name   | character(30) |
Indexes:
    "statename_pkey" PRIMARY KEY, btree (code)

test=# \d customer
          Table "public.customer"   
    Column   |     Type      | Modifiers
-------------+---------------+-----------
 customer_id | integer       |
 name        | character(30) |
 telephone   | character(20) |
 city        | character(25) |
 street      | character(40) |
 state       | character(2)  |
 zipcode     | character(10) |
 country     | character(20) |

Here's the command I'm running:

test=# ALTER TABLE customer ADD CONSTRAINT
state FOREIGN KEY (code) REFERENCES
statename (code) >MATCH FULL;

Here's the error I'm getting:

ERROR:  column "code" referenced in foreign key constraint does not exist

I'm looking at the column! I know it exists! Please help a brother out!

Derek · Accepted Answer · 2013-06-07 15:21:07Z

3

Code does not exist in your customer table. It's state.

ALTER TABLE customer 
ADD CONSTRAINT state FOREIGN KEY (state) 
REFERENCES statename (code) >MATCH FULL;

answered Jun 7, 2013 at 15:21

Derek

23.5k8 gold badges55 silver badges61 bronze badges

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

2 Comments

Line Over a year ago

@Derek, which "state" determines foreign key and which column name? I try to add reference without giving it's name - such as by table creating.

Chris Stryczynski Over a year ago

So essentially the column name should not be specified but instead just the table name?

Collectives™ on Stack Overflow

PostgreSQL: Column Reference in Foreign Key Constraint Does Not Exist

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