//specifies the primary key as a table constraint
create table employee (
id number(3),
name varchar2(30),
constraint pk_employee PRIMARY
KEY (id)
);
or
// specifies the primary key
constraint in the field (column constraint)
id number(3) constraint pk_employee PRIMARY KEY
Isn't it that Oracle autogenerates an identifier/constraint_name if you do not specify it? What is the use of specifying the constraint_name in such a case?
BTW, I learned that it is necessary
to supply a constraint_name when specifying a PRIMARY KEY table constraint.
How come that it is allowed in column constraints to skip the constraint_name,
while in table constraints there will be an error saying that the datatype
is undefined.
- you want to be able to query the *_constraints table and by the constraint_name understand what the constraint means.
- you might want to disable/enable/enforce constraints during loads and what not. having an assigned name using a naming convention makes this easy (eg: to disable the priamary key constraint on the emp table you want to disable the EMP_PK constraint -- emp plus Pk for primary key, rather then discover what the name is by querying the user_constraints table)...
- if you export/import frequently with ignore=y, you want to have EXPLICITY named everything else you'll end up with duplicate constraints frequently.
Consider:
SQL> create table t ( x int check (x >0) );
SQL> !exp userid=tkyte/tkyte
tables=t
Export: Release 8.0.3.0.0
- Production on Sat Mar 6 20:20:30 1999
(c) Copyright 1997 Oracle
Corporation. All rights reserved.
Connected to: Oracle8 Enterprise
Edition Release 8.0.3.0.0 - Production
With the Partitioning and
Objects options
PL/SQL Release 8.0.3.0.0 -
Production
Export done in US7ASCII character
set and US7ASCII NCHAR character set
About to export specified
tables via Conventional Path ...
. . exporting table
T 0 rows exported
Export terminated successfully
without warnings.
SQL> drop table t;
SQL> !imp userid=tkyte/tkyte
full=y
Import: Release 8.0.3.0.0 -
Production on Sat Mar 6 20:20:49 1999
(c) Copyright 1997 Oracle
Corporation. All rights reserved.
Connected to: Oracle8 Enterprise
Edition Release 8.0.3.0.0 - Production
With the Partitioning and
Objects options
PL/SQL Release 8.0.3.0.0 -
Production
Export file created by EXPORT:V08.00.03
via conventional path
. importing TKYTE's objects
into TKYTE
. . importing table
"T" 0 rows imported
Import terminated successfully
without warnings.
SQL> select constraint_name from user_constraints where table_name = 'T';
CONSTRAINT_NAME
------------------------------
SYS_C0039121
SQL> !imp userid=tkyte/tkyte
ignore=y full=y
Import: Release 8.0.3.0.0
- Production on Sat Mar 6 20:21:32 1999
(c) Copyright 1997 Oracle
Corporation. All rights reserved.
Connected to: Oracle8 Enterprise
Edition Release 8.0.3.0.0 - Production
With the Partitioning and
Objects options
PL/SQL Release 8.0.3.0.0 -
Production
Export file created by EXPORT:V08.00.03
via conventional path
. importing TKYTE's objects
into TKYTE
. . importing table
"T" 0 rows imported
Import terminated successfully
without warnings.
SQL> select constraint_name from user_constraints where table_name = 'T';
CONSTRAINT_NAME
------------------------------
SYS_C0039121
SYS_C0039122
SQL> !imp userid=tkyte/tkyte
ignore=y full=y
Import: Release 8.0.3.0.0
- Production on Sat Mar 6 20:21:47 1999
(c) Copyright 1997 Oracle
Corporation. All rights reserved.
Connected to: Oracle8 Enterprise
Edition Release 8.0.3.0.0 - Production
With the Partitioning and
Objects options
PL/SQL Release 8.0.3.0.0 -
Production
Export file created by EXPORT:V08.00.03
via conventional path
. importing TKYTE's objects
into TKYTE
. . importing table
"T" 0 rows imported
Import terminated successfully
without warnings.
SQL> select constraint_name from user_constraints where table_name = 'T';
CONSTRAINT_NAME
------------------------------
SYS_C0039121
SYS_C0039122
SYS_C0039123
See, everytime we run IMP,
it recreates the check constraint. Since the
constraint doesn't have a
name, it can create it over and over again. If we had
named the constraint, this
would not occur.
>BTW, I learned that it is
necessary to supply a constraint_name when
>specifying a PRIMARY KEY
table constraint. How come that it is allowed in
>column constraints to skip
the constraint_name, while in table constraints
>there will be an error saying
that the datatype is undefined.
>
no its not necessary:
SQL> create table t ( x int,
y int, z int, primary key(x,y) )
2 /
Table created.
SQL> select constraint_name from user_constraints where table_name = 'T';
CONSTRAINT_NAME
------------------------------
SYS_C0039119
> Can anyone explain the user of a constraint name
>Isn't it that Oracle autogenerates
an identifier/constraint_name
>if you do not specify it?
True, but then your errors also show this auto-generated name.
>I learned that it is necessary
to supply a constraint_name
>when specifying a PRIMARY
KEY table constraint.
The following syntax is allowed as well:
create table
employee (
id number(3),
name varchar2(30),
primary key(id, name)
);