Home » RDBMS Server » Server Utilities » Not Null Constraints Disappeared after import
Not Null Constraints Disappeared after import [message #220073] Sun, 18 February 2007 10:54 Go to next message
neemic
Messages: 4
Registered: May 2004
Junior Member
Hi there, I'm looking for help with a mystery...

I have a schema in Live which has lost all (thousands!) of it's Not null constraints. Investigation points to a recent import being the culprit. The schema's foreign key constraints were disabled and the tables truncated before the import. Also before the import, it looks like all constraints (fk, pk, uq and not null) were dropped. Not sure why this exact course of action was followed, but I would still have expected all contraints to import Ok. It seems only the Not Null constraints (which have system-generated names) have failed to restore.

Any plausible explanations would be most welcome, cos I will have to give chapter and verse on this to the man upstairs...

Thanks in advance,

neemic
Re: Not Null Constraints Disappeared after import [message #220112 is a reply to message #220073] Sun, 18 February 2007 22:26 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
consider
if you truncate table then NOT NULL constraint will not drop.
Of about not null constriant is not include in constraint it include in TABLE DEFINATION.

SQL> select constraint_name,constraint_type from user_constraints where table_na
me = 'TEE';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C005019                    C
PK_TEE                         P

SQL> host exp scott/tiger file=t.dmp tables=tee

Export: Release 10.1.0.2.0 - Production on Mon Feb 19 08:22:47 2007

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                            TEE          2 rows exported
Export terminated successfully without warnings.

SQL> truncate table tee;

Table truncated.

SQL> alter table tee drop constraint SYS_C005019;

Table altered.

SQL> alter table tee drop constraint PK_TEE;

Table altered.

SQL> host imp scott/tiger file=t.dmp full=y IGNORE=Y

Import: Release 10.1.0.2.0 - Production on Mon Feb 19 08:24:11 2007

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                          "TEE"          2 rows imported
Import terminated successfully without warnings.

SQL> select constraint_name,constraint_type from user_constraints where table_na
me = 'TEE';

CONSTRAINT_NAME                C
------------------------------ -
PK_TEE                         P

SQL>

--------------------------------------------------------
Now we drop table and import with table defination.
SQL> drop table tee purge;

Table dropped.

SQL> host imp scott/tiger file=t.dmp full=y

Import: Release 10.1.0.2.0 - Production on Mon Feb 19 08:25:09 2007

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                          "TEE"          2 rows imported
Import terminated successfully without warnings.

SQL> select constraint_name,constraint_type from user_constraints where table_na
me = 'TEE';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C005022                    C
PK_TEE                         P

SQL>


regards
Taj
Previous Topic: sqlldr: loading data with date
Next Topic: LOGMINER -- ORA-06512: at "SYS.DBMS_LOGMNR",
Goto Forum:
  


Current Time: Tue Jul 02 16:26:40 CDT 2024