CODEWITHSUNDEEP
databasepostgresql
membersound
membersound

Reputation: 86845

How to create database schema with shared primary foreign key?

My goal is to have a subtable whose primary key and foreign key both are the same column, and reference to an ID of the main table.

CREATE TABLE main_table(
    id integer NOT NULL,
    //some fields
)

CREATE TABLE test(
    id integer NOT NULL,
    name varchar,
    CONSTRAINT test_pk PRIMARAY KEY (id),
    CONSTRAINT test_fk FOREIGN KEY (fk_id)
        REFERENCES main_table (id) MATCH SIMPLE
)

But this will create a table mapping with two columns: id[PK] and test_fk as foreign key column. How can I combine them?

Upvotes: 0

Views: 302

Answers (1)

user330315
user330315

Reputation:

You have misunderstood how the foreign key clause works. You list the names of existing columns in there. And listing the column names will create any new column. Any FK column must have already be defined in the "columns part" of the create table statement.

So your statement wouldn't work at all because the table test does not have a column named fk_id. You need to supply the name of the already defined column id there:

CREATE TABLE test(
    id integer NOT NULL,
    name varchar,
    CONSTRAINT test_pk PRIMARAY KEY (id),
    CONSTRAINT test_fk FOREIGN KEY (id) --- <<< this was wrong
        REFERENCES main_table (id) MATCH SIMPLE
)

Upvotes: 1

Related Questions