57

I was wondering can some give me an explanation on how to assign primary and foreign keys in pgAdmin?

I can't find any information online.

For example...I've got a Student table with all their details (address, d.o.b. and etc.). I'm going to add a student_number to the table and make it a primary key.

I just want to know how do I do that using pgAdmin? And if you may be kind to explain give me further information on using Primary Keys in postgreSQL (and pgAdmin). The same case with the foreign keys.

Mr Teeth
  • 1,269
  • 5
  • 19
  • 23

5 Answers5

112

Yes, there is a way to add Primary & Foreign Keys in pgAdmin.

Tested in pgAdmin III Ver.1.16.1 (Windows 7)

  1. Select the table you want
  2. Ctrl+Alt+Enter or right-click / Properties
  3. Select "Constraints" tab
  4. At the left-bottom side of the form you will see the option "Primary Key"
  5. Click add
  6. Select "Columns" tab
  7. Select the column you want as a key
  8. Click add

And you are all set.

You can fill more things if you want, but now you know how to get there.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Victor Barrantes
  • 2,258
  • 2
  • 20
  • 13
  • Does this add a key to the table itself, or just virtually in PGadmin? If it's the prior, wouldn't things break in your app? This method adds a constraint, not a column, so is it safe? – ahnbizcad Jun 23 '14 at 01:45
  • Is there a way to add a "regular index" for optimisation purposes (rather than constraint/logical purposes) - for a quicker search? I can't seem to find it in pgadmin3 (constraint only has constraint types). – kfmfe04 Mar 03 '16 at 07:49
25

There is no option in pgAdmin to add a column to an existing table and make it the primary key at the same time, because this is hardly possible.

A primary key column needs to hold unique non-null values. Upon adding a column to an existing table, it holds NULL values. So you have to enter unique values before you can add a UNIQUE or PRIMARY KEY constraint.

There is an exception to that rule, though: If you add a serial column, unique values are inserted automatically. In this case, you can also define it PRIMARY KEY right away:

ALTER TABLE student ADD COLUMN student_number serial PRIMARY KEY;

This works in PostgreSQL 9.1. I am not sure it does in older versions, too.

pgAdmin does not incorporate this special case for serial columns in the "New column..." dialog at this time (version 1.14).

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    see Victor Barrantes answer,n it seems Mr theeth discover pgadmin so there is few chance he fall in the scenario you describe here, despite your answer is very interressant. – Damien MIRAS Sep 29 '15 at 01:08
1

In Pgadmin3,

  1. Go to table which you want to add the PK or FK and right click and choose properties.

  2. Go to constraints tab.

  3. Choose Primary Key or Foreign Key in drop down list which beside of Add button.

  4. And than click on add button.

  5. Go to columns tab.

  6. Choose the column name in drop down list ,which you want to add .

  7. Click add button.

  8. Click Ok button.

    Hope it will helpful for you !

Community
  • 1
  • 1
Yadana Chaw
  • 11
  • 1
0

The below SQL will work

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name='table_name';
Thirumal
  • 8,280
  • 11
  • 53
  • 103
0

pgAdmin 4.29 doesn't allow you to create foreign keys in the same dialog as table creation. To create a foreign key, create the table first and then expand the table, column and foreign key nodes in the explorer. Chose "Create".

JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245