主页 / dba / 问题 / 49768
Accepted
ObAt
Asked: 2013-09-13 09:16:16 +0800 CST

重复键违反唯一约束

  • 772

当我使用这个查询时，一切都很好：

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

我创建了一个组合两个表的视图：

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;

我还创建了这个规则：

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;

当我执行这个查询时，我得到一个错误：

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

错误：重复键违反唯一约束“用户名”SQL 状态：23505

我不知道为什么会出现这个错误，用户名是一个唯一的列，但是当我更新这个列时我不应该给出任何问题。

-- 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;
postgresql unique-constraint

2 个回答

  1. Best Answer

    您的规则没有WHERE子句，并且任何更新都试图修改整个users表。

    您可以/应该添加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

    注意：我以前从未RULE在 Postgres 中使用过该系统。但上述建议在SQL-Fiddle 测试时有效。

    • 6 
  2. SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id)+1, 1), false) FROM users;
    • -1

相关问题