Add a reference to a postgrest table

user_8062200 0
Viewed 21

I want to alter the artist_label to be label_id BIGINT NOT NULL REFERENCES label(id) like how it is in the album_label table.

CREATE TABLE label (
    id               BIGSERIAL PRIMARY KEY,
    name             TEXT NOT NULL UNIQUE,
    parent_label_id  BIGINT REFERENCES label(id)
);

CREATE TABLE album_label (
    id              BIGSERIAL PRIMARY KEY,
    album_id        TEXT NOT NULL UNIQUE,
    label_id        BIGINT NOT NULL REFERENCES label(id)
);

CREATE INDEX album_label_idx ON album_label(label_id);
CREATE INDEX album_uid_idx ON album_label(id)

CREATE TABLE artist_label (
    artist_id         VARCHAR(60) NOT NULL,
    label_id          BIGINT NOT NULL,

    PRIMARY KEY (artist_id, label_id)
);

CREATE INDEX artist_label_idx ON artist_label(label_id);
postgresql sql
1 Answers

If you haven't created the tables yet, you could modify the creation script and add that clause.

If you're asking how to modify the table after its creation, you can use an alter table statement:

ALTER TABLE artist_label 
ADD CONSTRAINT artist_label_fk FOREIGN KEY (label_id) REFERENCES label(id)

SQLFiddle Example

user_24227760
Related