PostgreSQL creating fields that both fields cannot repeat

Question

Given table, would you please us how to set up the unique constraint so that first_name and last_name are not allowed to type the same field?

CREATE TABLE app_user (
   id serial PRIMARY KEY,
   first_name VARCHAR (50),
   last_name VARCHAR (50),
   email VARCHAR (50) UNIQUE
);


CREATE UNIQUE INDEX user_firstname_last_name_id ON app_test.app_user(first_name text_ops,last_name text_ops);

I have created unique constraint including (first_name, last_name) but to no avail.

If first_name is 'Chan' , last_name is 'Mandy' , the database triggers allows this record to be updated

If first_name is 'Chan' , last_name is 'Chan' , the database triggers does not allow this record to be updated

As for insert first time, the constraint check can be ignored

user is a reserved word(postgresql.org/docs/8.1/sql-keywords-appendix.html). — forpas
– forpas, Commented Jul 3, 2019 at 17:32

klin · Accepted Answer · 2019-07-03 17:44:30Z

1

Use a check constraint if you want the two columns not to contain the same values, e.g.

CREATE TABLE app_user (
   id serial PRIMARY KEY,
   first_name VARCHAR (50),
   last_name VARCHAR (50),
   email VARCHAR (50) UNIQUE,
   CHECK(first_name <> last_name)
);

insert into app_user (first_name, last_name)
values('Adam', 'Adam');

ERROR:  new row for relation "app_user" violates check constraint "app_user_check"
DETAIL:  Failing row contains (2, Adam, Adam, null).

answered Jul 3, 2019 at 17:44

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Bootsholz Over a year ago

How can we apply check only if the user record is UPDATE instead of INSERT?

klin Over a year ago

The check constraint applies to the table, not to statements. It works both when inserting and updating rows.

Belayer · Accepted Answer · 2019-07-03 21:24:25Z

Check constraints apply to all table DML operation (well Insert and Update) enteries. So what you want cannot be done with constraints. You can accomplish it with trigger.

CREATE TABLE app_user (
   id serial PRIMARY KEY,
   first_name VARCHAR (50),
   last_name VARCHAR (50),
   email VARCHAR (50) UNIQUE
); 

create or replace function reject_same_name()
returns trigger
language plpgsql
as $$
begin 
    if old.first_name = old.last_name then
       raise exception 'Cannot update row when first name and last name are the same.';
    end if;
    return new;
end ;
$$;

insert into app_user (first_name, last_name)
values('Adam', 'Adam');
-- 1 row 

insert into app_user (first_name, last_name)
values ('Sam','Henery');
-- 1 row 
select * from app_user;

update app_user
   set email = first_name || '.' || last_name || '@mydb.com' 
 where first_name = 'Sam'; 
-- 1 row updated

update app_user
   set email = first_name || '.' || last_name || '@mydb.com' 
 where first_name = 'Adam';  -- Is this really what we want??
********** Error **********

ERROR: Cannot update row when first name and last name are the same.
SQL state: P0001

So far so good? But what happens when we realize an mistake was made: Like the last name was inserted incorrectly so we correct to:

update app_user set last_name = 'Smith' 
  where last_name = 'Adam';

So that trigger function may need lots of work deciding the business rule as to what can and what cannot be updated and when. Triggers are really a bad ways to enforce business rules.

Collectives™ on Stack Overflow

PostgreSQL creating fields that both fields cannot repeat

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related