Search code examples
postgresqlforeign-keysprimary-keypgadmin

Primary & Foreign Keys in pgAdmin

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.

Solution

  • 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).