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

Question

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?

• Jack Douglas: Please ask any follow-up or tangentially related questions separately

Answer 1

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

Answer 2

Apart from using named constraints, you can add/remove a NOT NULL constraint in a column without any name.

Let's imagine we have the following table (Oracle):

 CREATE TABLE t
 (
 a numeric PRIMARY KEY,
 b numeric
 ) ;
 INSERT INTO t (a,b) VALUES (1, 2);

If you want to change the table and force column b to hold only NOT NULL values, you can do it by using the following statement (that doesn't require to name the constraint):

 ALTER TABLE t 
 MODIFY (b NOT NULL) ;

If you were to try this when there's already a NULL value on column b, you'd get the error:

ORA-02296: cannot enable (xxxxxxxxxx) - null values found

If you would like to change back the column to allow NULL values, you can do it with the following DDL, which doesn't require that you know any constraint name:

 ALTER TABLE t
 MODIFY (b NULL) ;

dbfiddle here