Does any kind of constraint in a database have a unique constraint name

constraintoracleoracle-11g-r2

I know that it is possible and even encouraged to add primary key constraints and foreign key constraints after the creation of the table so that the programmer has control over the names of the constraints. If the programmer adds these constraints when the table is created (without specific add constraint syntax) then the Database Engine gives a name to these constraints that is comprised of random characters. The name that the database engine gives to these 'non-explicit' constraints depends on the implementation.

However my question is: Does ANY kind of constraint have a specific name in the database engine? Even the NOT NULL constraints ? Can those simple constraints like NOT NULL be referenced at a later time in an "ALTER TABLE" command?

Best Answer

I know that it is possible and even encouraged to add primary key constraints and foreign key constraints after the creation of the table so that the programmer has control over the names of the constraints. If the programmer adds these constraints when the table is created (without specific add constraint syntax) then the Database Engine gives a name to these constraints that is comprised of random characters.

Are you saying constraint names can't be given at table creation time? That isn't correct:

create table foo(id integer primary key);
create table bar(id integer constraint pk_bar primary key);
select table_name, constraint_name from user_constraints;
TABLE_NAME | CONSTRAINT_NAME
:--------- | :--------------
FOO        | SYS_C007856    
BAR        | PK_BAR         

dbfiddle here

However my question is: Does ANY kind of constraint have a specific name in the database engine? Even the NOT NULL constraints ? Can those simple constraints like NOT NULL be referenced at a later time in an "ALTER TABLE" command?

It's exactly the same for NOT NULL constraints:

create table foo(id integer not null);
create table bar(id integer constraint nn_bar_id not null);
select table_name, constraint_name from user_constraints;
TABLE_NAME | CONSTRAINT_NAME
:--------- | :--------------
FOO        | SYS_C007860    
BAR        | NN_BAR_ID      

dbfiddle here

Can those simple constraints like NOT NULL be referenced at a later time in an "ALTER TABLE" command?

Of course, just like any other constraint:

alter table bar drop constraint nn_bar_id;

dbfiddle here