Re: constraint name: what for?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 07 Mar 1999 01:27:46 GMT
Message-ID: <36edd40e.14088988@192.86.155.100>

A copy of this was sent to jerwynn_at_my-dejanews.com (if that email address didn't require changing) On Sun, 07 Mar 1999 00:30:29 GMT, you wrote:

>Hi,
>
> Can anyone explain the user of a constraint name such as pk_employee in:
>
>//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?
>

yes, it will generate a unique name for you. *some* (there are lots) reasons you might want to name it yourself:

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
>
>
>
>
>Thanks.
>
>
>JErwynn
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Mar 06 1999 - 19:27:46 CST