SQL I dont want to have names like alfred or Alfred or alfr in the same table

Question

This is what I tried to do but it doesn't work :

ALTER TABLE DEPT
ADD CONSTRAINT DEPT_DNAME_CK CHECK (DNAME = 'ALF%');

Answer 1

If you want to allow a single value but then not anything similar - where 'similar' here seems to be starting with the same first three letters, in any case - you can use a unique function-based index:

CREATE UNIQUE INDEX UNQ_DNAME_START ON DEPT (UPPER(SUBSTR(DNAME, 1, 3)));

Unique index UNQ_DNAME_START created.

Then you can have one value:

INSERT INTO DEPT (DNAME) VALUES ('Alfred');

1 row inserted.

But attempting to insert a second similar value will error:

INSERT INTO DEPT (DNAME) VALUES ('alfonso');

Error report -
SQL Error: ORA-00001: unique constraint (SCHEMA.UNQ_DNAME_START) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

I'm assuming that you've only used 'alf%' as an example, and you actually want to prevent all similar entries, not that specific prefix.