Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 1, column 16 

CREATE TABLE x
(
  y INT GENERATED BY DEFAULT AS IDENTITY,
  n VARCHAR (25) NOT NULL
);

INSERT INTO x (n) VALUES ('Frodo Baggins' );
1 rows affected

INSERT INTO x (n) VALUES ('Luke Skywalker' );
1 rows affected

INSERT INTO x (n) VALUES ('Luke Skywalker');
1 rows affected

INSERT INTO x (n) VALUES ('Harry Potter');
1 rows affected

SELECT * FROM x;

Y
N

1
Frodo Baggins

2
Luke Skywalker

3
Luke Skywalker

4
Harry Potter

ALTER TABLE x
ADD CONSTRAINT n_no_longer_unique_ck CHECK (n NOT IN (SELECT n FROM x));

INSERT INTO x (n) VALUES ('Luke Skywalker');
Operation violates CHECK constraint N_NO_LONGER_UNIQUE_CK on view or table X At trigger 'CHECK_1' 

SELECT * FROM x;

Y
N

1
Frodo Baggins

2
Luke Skywalker

3
Luke Skywalker

4
Harry Potter

SELECT 
       y, 
       n, 
       ROW_NUMBER() OVER (PARTITION BY n ORDER BY y DESC) AS rn
FROM x ORDER BY n;

Y
N
RN

1
Frodo Baggins
1

4
Harry Potter
1

3
Luke Skywalker
1

2
Luke Skywalker
2

SELECT * FROM x;

Y
N

1
Frodo Baggins

2
Luke Skywalker

3
Luke Skywalker

4
Harry Potter

INSERT INTO x (n) VALUES ('New Name');
1 rows affected

SELECT * FROM x;

Y
N

1
Frodo Baggins

2
Luke Skywalker

3
Luke Skywalker

4
Harry Potter

6
New Name

UPDATE x SET n = 'Luke Skywalker' WHERE y = 6;
Operation violates CHECK constraint N_NO_LONGER_UNIQUE_CK on view or table X At trigger 'CHECK_2' 

SELECT * FROM x;

Y
N

1
Frodo Baggins

2
Luke Skywalker

3
Luke Skywalker

4
Harry Potter

6
New Name
Y	N	RN
1	Frodo Baggins	1
4	Harry Potter	1
3	Luke Skywalker	1
2	Luke Skywalker	2