0

How to update and set unit_id to be NULL, unit id is foreign key and I need to set to be NULL

CREATE TABLE troops
(
  id serial NOT NULL,
  unit_id integer,
  type integer NOT NULL,
  level integer NOT NULL,
  CONSTRAINT troops_pkey PRIMARY KEY (id),
  CONSTRAINT troops_unit_id_fkey FOREIGN KEY (unit_id)
      REFERENCES units (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

I have tried to update troops and set unit_id to be NULL ( I put NULL not 0 in pqxx statement in C++ ) but I get error like 

 insert or update on table "troops" violates foreign key constraint "troops_unit_id_fkey"
DETAIL:  Key (unit_id)=(0) is not present in table "units".

When I try from pgadmin I can set unit_id to be NULL in troops but pqxx ( from c++ code I try to update) converts NULL to 0, how to solve this ?

PaolaJ.
  • 10,872
  • 22
  • 73
  • 111
  • As the error message describes you are using a foreign key check, so that any time you enter a value in the column, the database will check to see if that value exists. Presumably your primary key starts from the number 1 so zero will not be present. – Lucas Jul 22 '14 at 12:06
  • 2
    The `NULL` c++ constant is acutally `0` (or `0L`) -- use `NULL` inline your sql, or: http://stackoverflow.com/questions/21574102/inserting-null-empty-string-using-libpqxx-library – pozs Jul 22 '14 at 12:33

0 Answers0