Relation "table" already exists

user_8779660 0
Viewed 13026

I'm building a SQL database and running into the error "relation 'sexes' already exists".

Per SO users that posted about a similar problem, I've attempted: commenting out constraints to see if those were interfering, changing all references to serial/ all primary keys to integers to match up types, dropping all tables if they exist. The error remains the same no matter what I do so I've posted my original code. I could really use some guidance as to what the issue in this case is. Thanks! 

CREATE TABLE sexes (
  id                serial PRIMARY KEY,
  string            text NOT NULL
);

CREATE TABLE humans (
  id                serial PRIMARY KEY UNIQUE NOT NULL,
  forename          text NOT NULL,
  surname           text NOT NULL,
  birthdate         date,
  sex_id            integer REFERENCES sexes(id)
);

CREATE TABLE marriages (
  id                serial PRIMARY KEY UNIQUE NOT NULL,
  partner_1_id      integer REFERENCES humans(id),
  partner_2_id      integer REFERENCES humans(id),
  marriage_date     date NOT NULL,
  divorce_date      date NOT NULL,
  CONSTRAINT divorced CHECK (marriage_date <= divorce_date),
  CONSTRAINT marry_self CHECK (partner_1_id <> partner_2_id) 
);
atom-editor postgresql sql
1 Answers

You could use IF NOT EXISTS clause:

Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the one that would have been created.

CREATE TABLE IF NOT EXISTS sexes (
  id                serial PRIMARY KEY,
  string            text NOT NULL
);

Which is really strange because dropping the tables if they existed before creating new ones threw back a whole new error.

If you want to drop tables you have to do it in right order:

DROP TABLE IF EXISTS marriages;
DROP TABLE IF EXISTS humans;
DROP TABLE IF EXISTS sexes;
user_50708790
Related