1

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?

  • 1
    Why do you need unique on username AND emp_name? Isn't username in and of itself unique? Do you actually have multiple people with the same username? Beyond that, see the 'community wiki' answer. Your sample data is NOT unique across both username and emp_name. – EdStevens Mar 4 '18 at 13:32
2

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.

  • 1
    I' m using Oracle, but the syntax provided both in the post and found within the documentation was applicable to my code – BOB Mar 4 '18 at 0:32
0

Community wiki answer:

Works as intended:

You created a constraint that ensures the uniqueness of username and emp_name together.

Then you inserted a second row with the same username, but different emp_name value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.