Introduction
Primary keys
Foreign keys
Updating and deleting with foreign keys
Instruction
Nicely done! Now, we have the following table:
CREATE TABLE soccer_player ( id integer PRIMARY KEY, first_name varchar(32), last_name varchar(32), team_id integer, FOREIGN KEY (team_id) REFERENCES soccer_team (id) );
We want to get rid of the foreign key. We can use the following syntax:
ALTER TABLE soccer_player DROP CONSTRAINT soccer_player_team_id_fkey;
The syntax for dropping foreign and primary keys is identical. The only thing you should keep in mind is PostgreSQL's default naming convention. For foreign keys, it's: the table_name + _ + column_name + _fkey. That's why the column named team_id in the soccer_player table (which is a foreign key) has a constraint named soccer_player_team_id_fkey.
Exercise
The table employee is currently created like this:
CREATE TABLE employee ( id integer PRIMARY KEY, first_name varchar(32), last_name varchar(32), department_id integer, position_id varchar, FOREIGN KEY (department_id) REFERENCES department (id) );
However, our company decided to create a new business model in which there are no fixed departments – each employee can work for multiple divisions. Your task is to get rid of the foreign key department_id.
