console.jay()

PostgreSQL Primary Key and Foreign Key

2 min read

Primary Key

We can assign a primary key to a single column or to a combination of columns, either when creating a table (CREATE TABLE) or when altering a table (ALTER TABLE).

CREATE TABLE person (
	id BIGSERIAL PRIMARY KEY,
	name VARCHAR(100)
);
 
CREATE TABLE person (
	id BIGSERIAL,
	name VARCHAR(100),
	PRIMARY KEY (id, name)
);
 
ALTER TABLE person ADD PRIMARY KEY (id);
ALTER TABLE person ADD PRIMARY KEY (id, name);

To remove a primary key from a table, you can use the ALTER TABLE statement to do it. Find the name of the primary key by using \d table_name before removing it.

ALTER TABLE person DROP CONSTRAINT person_pkey;

Foreign Key

We can create a relationship between two tables by using a foreign key from the child table to create a link to the primary key of the parent table. Creating a relationship between two tables can enable us to perform some useful commands, like JOIN, to join two tables.

CREATE TABLE person (
	id BIGSERIAL PRIMARY KEY,
	name VARCHAR(100)
);
 
CREATE TABLE car (
	id BIGSERIAL PRIMARY KEY,
	person_id BIGINT,
	FOREIGN KEY (person_id) REFERENCES person (id)	
);

To remove a foreign key from a table, you can also use the ALTER TABLE statement do to it.

ALTER TABLE car DROP CONSTRAINT car_person_id_fkey;

References

Graph View

Backlinks