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%');

in the end i dont want to be able to enter upper or lower or similar names in the table. the answer below gave me this error:ORA-02293 — Alfred.S.3, Jul 20 '15 at 06:39
That error is constraint `ORA-02290: check constraint` violation error. If you have below check constraint and insert a data that violate the constraint, then you get this error. — Praveen, Jul 20 '15 at 06:48
Do you want to prevent anything starting with 'alf' (in any case), or allow a single value and prevent a second similar value being inserted? It also isn't clear if only 'alf' needs to be restricted, or anything with similar first three characters. — Alex Poole, Jul 20 '15 at 08:41

score 1 · Answer 1 · edited Jul 20 '15 at 07:24

If there is some entries in the column dname like alf%, you have to delete the entries before you add the constraint.

create table dept (dname varchar(250));
insert into dept select 'alflll' from dual;
alter table dept add constraint dept_dname_ck check (lower(dname) not like 'alf%');

you get an error;

ORA-02293: cannot validate (*****.DEPT_DNAME_CK) - check constraint violated

now delete the entries:

delete from dept where lower(dname) like 'alf%';
alter table dept add constraint dept_dname_ck check (lower(dname) not like 'alf%');

After you enable this constraint, if you try to violate the constraint you get an error:

ORA-02290: check constraint (****.DEPT_DNAME_CK) violated

score 0 · Accepted Answer · answered Jul 20 '15 at 08:56

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.

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

2 Answers2