Primary and foreign keys in pgAdmin

Question

I was wondering if someone could explain to me how to assign primary and foreign keys in pgAdmin?

I can not find any information on the Internet.

For example ... I have a Student table with all their details (address, dob, etc.). I am going to add student_number to the table and make it the primary key.

I just want to know how to do this using pgAdmin? And if you can be kind to explain, give me more information about using Primary keys in postgreSQL (and pgAdmin). The same case with foreign keys.

Answer 1

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

The primary key column must contain unique non-zero values. After adding a column to an existing table, it contains NULL values. Therefore, before adding UNIQUE or PRIMARY KEY you must enter unique values.

There is an exception to this rule. If you add serial , unique values ​​are inserted automatically. In this case, you can also immediately determine its PRIMARY KEY:

 ALTER TABLE student ADD COLUMN student_number serial PRIMARY KEY; 

This works in PostgreSQL 9.1. I'm also not sure if this is done in older versions.

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

Answer 2

Yes, there is a way to add primary and foreign keys in pgAdmin.

Tested in pgAdmin III Ver.1.16.1 (Windows 7)

• Select the desired table
• Ctrl + Alt + Enter or right click / Properties
• Select the Limitations Tab
• In the lower left part of the form you will see the option "Primary key"
• Press button
• Select the Columns tab
• Select the desired column as the key
• Press button

And you are all set.

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

Answer 3

Below SQL works

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

Answer 4

In Pgadmin3,

• Go to the table you want to add PK or FK, and right-click and select properties.
• Click on the restrictions tab.
• Select Primary Key or Foreign Key from the drop-down list next to the Add button.
• And click the "Add" button.
• Click on the column tab.
• Select the column name in the drop-down list that you want to add.
• Click the Add button.

• Click the OK button.

  Hope this helps you!