CODEWITHSUNDEEP
sqlpostgresqlforeign-keys
Patrick
Patrick

Reputation: 339

How to add a foreign key when creating a table to a table created after it?

How does it work when I want to add a foreign key to the first table that I make that references to the primary key of the second table I create?

CREATE TABLE table1
(   
    name_id INT NOT NULL,
    team TEXT REFERENCES table2(team_id),
    PRIMARY KEY(name_id)
);

CREATE TABLE table2
(
    team_id INT NOT NULL,
    teamname TEXT,
    PRIMARY KEY(team_id)
);

If I try the code above, I get the following error:

ERROR: relation "" does not exist

Upvotes: 2

Views: 2018

Answers (2)

parsa
parsa

Reputation: 985

here's the syntax of creating a table with Foreign key:

CREATE TABLE table11
(   
    name_id INT NOT NULL,
    team INT,
    PRIMARY KEY(name_id),
    foreign key(team) references table22(team_id)
);



CREATE TABLE table22
(
    team_id INT NOT NULL,
    teamname TEXT,
    PRIMARY KEY(team_id)
);

but there was another problem. a foreign key from a child table cannot reference to a primary key from a parent folder if they do not contain the same type. in your code team was of TEXT and team_id was of INT which cannot be.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Either create the second table first. Or use alter table. That is, create the first table without the reference and then do:

alter table table1 add constraint fk_table1_team
    foreign key (team_id) REFERENCES table2(team_id);

The declaration for table1 would be:

CREATE TABLE table1 (   
    name_id INT NOT NULL,
    team_id INT, 
    PRIMARY KEY(name_id)
);

The reference between the tables should be on the primary key and certainly not on a character column, if an integer is available.

Upvotes: 1

Related Questions