Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table person (
 id                             serial	 not null 		,
 full_name                     	varchar(25)	          	 	,
 role                          	varchar(25)	          	 	,
 first_name                    	varchar(25)	          	 	,
 last_name                     	varchar(25)	          	 	,
 linkedin_slug                 	varchar(25)	          	 	,
 email                         	varchar(25)	          	 	,
 domain                        	varchar(25)	          	 	,
 created_at                    	 timestamp 	default	 now()	,
 updated_at                    	 timestamp 	default	 now()	,
 domainRoot varchar(25) GENERATED ALWAYS AS ( LEFT(domain, STRPOS(domain,'.')-1)) STORED,
 l_fname varchar(25) GENERATED ALWAYS AS ( LOWER(full_name)) STORED,
 CONSTRAINT "pk_person" PRIMARY KEY (id),
 CONSTRAINT  "uq_person_full_name_domain" UNIQUE (full_name, domain),
 CONSTRAINT  "uq_lower_full_name" UNIQUE (l_fname),
 CONSTRAINT "uq_email" UNIQUE (email),
 CONSTRAINT "ck_valid_email" CHECK (email LIKE '%@%.%'),
 CONSTRAINT "uq_domain_root" UNIQUE (domainRoot)
);
insert into person (full_name) values ('Joe Bloggs'),('Mrs Brown')
2 rows affected

insert into person (full_name) values ('Joe BLOGGS')
ERROR:  duplicate key value violates unique constraint "uq_lower_full_name"
DETAIL:  Key (l_fname)=(joe bloggs) already exists.


update person set email = 'invalid', updated_at = now()
ERROR:  new row for relation "person" violates check constraint "ck_valid_email"
DETAIL:  Failing row contains (1, Joe Bloggs, null, null, null, null, invalid, null, 2022-04-08 16:22:03.749316, 2022-04-08 16:22:03.751928, null, joe bloggs).


update person set email = 'ok@dom.com' where id = 2;
1 rows affected

update person set domain = 'hello.com' , updated_at = now() where id = 1;
1 rows affected

update person set domain = 'hello.UK' where id = 2
ERROR:  duplicate key value violates unique constraint "uq_domain_root"
DETAIL:  Key (domainroot)=(hello) already exists.


SELECT * FROM person


id
full_name
role
first_name
last_name
linkedin_slug
email
domain
created_at
updated_at
domainroot
l_fname


2
Mrs Brown
null
null
null
null
ok@dom.com
null
2022-04-08 16:22:03.749316
2022-04-08 16:22:03.749316
null
mrs brown

1
Joe Bloggs
null
null
null
null
null
hello.com
2022-04-08 16:22:03.749316
2022-04-08 16:22:03.753807
hello
joe bloggs

id	full_name	role	first_name	last_name	linkedin_slug	email	domain	created_at	updated_at	domainroot	l_fname
2	Mrs Brown	null	null	null	null	ok@dom.com	null	2022-04-08 16:22:03.749316	2022-04-08 16:22:03.749316	null	mrs brown
1	Joe Bloggs	null	null	null	null	null	hello.com	2022-04-08 16:22:03.749316	2022-04-08 16:22:03.753807	hello	joe bloggs