Postgresql – Duplicate key violates unique constraint

postgresqlunique-constraint

When I use this query everything is fine: 

UPDATE users 
   SET f_name='Mike', 
       l_name='MyLastName', 
       username='blabla', 
       "password"='asdfsdaf', 
       "class"=12, 
       lang='en' 
 WHERE id=50;

I created a view which combines two tables: 

CREATE OR REPLACE VIEW students_data AS 
SELECT students.id, 
       users.f_name, 
       users.l_name, 
       users.username, 
       users."password", 
       users."class", 
       users.permission, 
       users.sessionid, 
       users.lastlogin, 
       users.lang
  FROM students
  LEFT JOIN users ON students.id = users.id;

And I also created this rule: 

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD  
       UPDATE users 
          SET f_name = new.f_name, 
              l_name = new.l_name, 
              username = new.username, 
              "password" = new."password", 
              "class" = new."class", 
              permission = new.permission, 
              sessionid = new.sessionid, 
              lastlogin = new.lastlogin, 
              lang = new.lang;

When I execute this query I get an error: 

UPDATE students_data 
   SET f_name='Mikaa', 
       l_name='MikeL', 
       username='blabla', 
       "password"='asdfsdaf', 
       "class"=12, 
       lang='en' 
 WHERE id=50;

ERROR: duplicate key violates unique constraint "username" SQL
status:23505

I have no idea why I get this error, username is a unique column but I shouldn't give any problems when I update this column. 

-- Table: users

-- DROP TABLE users;

CREATE TABLE users
(
  id serial NOT NULL,
  f_name character varying,
  l_name character varying,
  username character varying NOT NULL,
  "password" character varying NOT NULL,
  "class" integer NOT NULL,
  permission integer NOT NULL,
  sessionid character varying,
  lastlogin integer,
  lang character varying(5),
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT "class" FOREIGN KEY ("class")
      REFERENCES "class" (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT username UNIQUE (username)
)
WITHOUT OIDS;

Best Answer

Your rule does not have a WHERE clause and any update is trying to modify the whole users table.

You can/should add WHERE id = old.id:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD  
UPDATE users 
SET f_name = new.f_name, 
    l_name = new.l_name,
    --- ...
WHERE id = old.id ;               --- added

Note: I had never used the RULE system before in Postgres. But the above suggestion worked when tested at SQL-Fiddle.

Related Question