Unique constraint insert prevention

oracleunique-constraint

I've created a unique constraint to my table containing employee account details in an attempt to stop duplicated data inserts.

ALTER TABLE emp ADD CONSTRAINT uq_ UNIQUE (USERNAME, EMP_NAME);

However my problem comes when it comes inserting the test data:

----this first insert should work fine
INSERT INTO table_name (id, username, emp_name, regdate)
VALUES (1, 'test', 'test1', sysdate);

What I'm trying to prevent is an instance like this were the data about an employee is inserted twice into the table:

INSERT INTO table_name (id, username, emp_name, regdate)
VALUES (2, 'test', 'test2', sysdate);

As at the moment the insert runs and adds to the table which I'm trying to prevent it from doing.

Is there a way to combat this, would a trigger be a better option to use?

Best Answer

Creating a proper unique constraint on your table will prevent duplicates amongst the columns specified.

Your naming of the constraint should be unique to the table itself. All constraints for a database are listed under the pg_constraint table.

ALTER TABLE emp ADD CONSTRAINT uq_emp_username_emp_name UNIQUE (USERNAME, EMP_NAME);

There are quite a few docs on the web you can find about standardized SQL naming formats.

To do a check against both the USERNAME and EMP_NAME, you would need to specify constraints individually for each.

ALTER TABLE emp ADD CONSTRAINT uq_emp_username UNIQUE (USERNAME);
ALTER TABLE emp ADD CONSTRAINT uq_emp_emp_name UNIQUE (EMP_NAME);

This would prevent employees from creating multiple usernames under the same employee name.

I find that the PostgreSQL official documentation is super concise and well written. https://www.postgresql.org/docs/9.5/static/ddl-constraints.html Just make sure you have your appropriate Postgres version selected.

Related Solutions

How to directly query other user tables

Just in case somebody else needs, I am posting the solution I used. Instead of using fromuser and touser options, I used remap_schema option:

> impdp user1/password DIRECTORY=backup_dir DUMPFILE=Dump.dmp FULL=Y
> remap_schema=user2:user1

Also, if the tablespaces are different, they must be remapped, too:

> impdp user1/password DIRECTORY=backup_dir DUMPFILE=Dump.dmp FULL=Y
> remap_schema=user2:user1 remap_tablespace=table2:table1

PostgreSQL UNIQUE constraint error message

For instance, you can read these separate fields mentioned in your quote inside exception handlers in PL/pgSQL (as instructed in the manual, like @dezso commented):

DO
$$
DECLARE
   err_constraint text;

BEGIN
   INSERT INTO recipes (title) VALUES ('foo'), ('foo');  -- provoke unique violation

EXCEPTION
   WHEN SQLSTATE '23000' THEN  -- Class 23 — Integrity Constraint Violation
      GET STACKED DIAGNOSTICS err_constraint = CONSTRAINT_NAME;
      -- do something with it, for instance:
      RAISE NOTICE '%', err_constraint;
      RAISE;  -- raise original error

END;
$$

Result:

NOTICE:  title_unique

ERROR:  duplicate key value violates unique constraint "title_unique"
DETAIL:  Key (title)=(foo) already exists.
CONTEXT:  SQL statement "INSERT INTO recipes (title) VALUES ('foo')"
PL/pgSQL function inline_code_block line 5 at SQL statement

Aside: you declare the unique constraints twice in your DDL command. (To my surprise, this results in a single constraint in my test on pg 9.5). Either way, do it only once.

CREATE TABLE recipes (
    id SERIAL,
    title TEXT NOT NULL,  -- remove redundant clause
    description TEXT NOT NULL,
    instructions TEXT NOT NULL,
    image BYTEA,
    CONSTRAINT recipes_pk PRIMARY KEY(id),
    CONSTRAINT title_unique UNIQUE(title)
);

Best Answer

Related Solutions

How to directly query other user tables

PostgreSQL UNIQUE constraint error message

Related Question