What Is the Default Constraint Name in Oracle?

Problem:

You want to know the default names of constraints in a table in Oracle.

Example:

In our database, we create two tables, country and student, with the following constraints: PRIMARY KEY (the column

in the tables country and student), FOREIGN KEY (the column country_id in the table student), DEFAULT (the column name in the table student), UNIQUE (the column name in the table country and the column personal_number in the table student), and CHECK (the column age in the table student).

Solution:

CREATE TABLE country (
id int NOT NULL primary key,
name varchar(100) UNIQUE
);

CREATE TABLE student (
id int NOT NULL primary key,
name varchar(100) DEFAULT 'UNKNOWN',
personal_number varchar(100) UNIQUE,
age int CHECK(age>15),
country_id int,
FOREIGN KEY(country_id) references country(id)
);

Let’s display how Oracle names the constraints of these tables by default. Use this query to see them:

select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
from USER_CONSTRAINTS
where TABLE_NAME='STUDENT' OR TABLE_NAME='COUNTRY';

Here is the result:

CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME
SYS_C007376	R	STUDENT
SYS_C007366	P	COUNTRY
SYS_C007374	P	STUDENT
SYS_C007367	U	COUNTRY
SYS_C007375	U	STUDENT
SYS_C007372	C	STUDENT

Discussion:

When we create our tables, we set constraints for some columns. However, we don’t specify the names for these constraints, so they are named by default. How are the default names for constraints generated?

In Oracle, the default constraint name starts with the username, followed by ‘_C’ and a sequence number. The types of constraint are ‘C’ (check), ‘P’ (primary key), ‘U’ (unique constraint), and ‘R’ (foreign key). All default constraint names are generated as ‘SYS’, ‘_’, and the number. You can see the default names in the table above. For example, the CHECK constraint for the column age in the student table has the name SYS_C007372.