How to only insert rows that do not violate a specific constraint

PostgreSQL offers an alternative to raising an exception upon a unique key violation: ON CONFLICT ON CONSTRAINT

INSERT INTO sales.sales_history 
SELECT * from sales.sales_history_p201801 
ON CONFLICT ON CONSTRAINT uc_sh1 DO NOTHING;

Comes in as a handy way of transfering rows from an old table to a partially populated new table. Duplicate rows will simply be ignored.

Documentation for PostgreSQL 11 can be found here.

Simple script for finding a leftover FK constraint to an interim table after a redefinisjon

After a redefinition of a table, you may be stuck with some FK constraints from other tables, to the old interim tables.

This simple script fixed my problem:

alter session set nls_language='american';
set lines 200
col table_name format a30
col constraint_name format a30
col r_constraint_name format a30
col status format a20

SELECT  TABLE_NAME, CONSTRAINT_NAME
FROM    DBA_CONSTRAINTS
WHERE   OWNER = '&&owner'
AND     CONSTRAINT_TYPE='R'
AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&&interim_table');

prompt copy and paste output above as input to owner and table_name:
prompt
ALTER TABLE &&owner..&&table_name DROP CONSTRAINT &constraint_name;
DROP TABLE &&owner..&&interim_table PURGE;
exit

Result:

Enter value for owner: SCOTT
old   3: WHERE   OWNER = '&&owner'
new   3: WHERE   OWNER = 'SCOTT'
Enter value for interim_table: ORDERS_INTERIM
old   5: AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&&interim_table')
new   5: AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='ORDERS_INTERIM')

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
ORDERROWS                      TMP$$_FK__ORD__ENT0

copy and paste output above as input to owner and table_name:
Enter value for table_name: ORDERROWS
Enter value for constraint_name: TMP$$_FK__ORD__ENT0
old   1: ALTER TABLE &&owner..&table_name DROP CONSTRAINT &constraint_name
new   1: ALTER TABLE SCOTT.ORDERROWS DROP CONSTRAINT TMP$$_FK__ORD__ENT0

Table altered.

old   1: DROP TABLE &&owner..ORDERS_INTERIM PURGE
new   1: DROP TABLE SCOTT.ORDERS_INTERIM PURGE

Table dropped.

How to create a unique constraint with an existing index (the USING-clause)

Create the index:

CREATE UNIQUE INDEX MYTABLE_IDX1 ON MYTABLE
(ID,CATEGORY)
LOCAL
TABLESPACE USERS
ONLINE;

Create a unique constraint:

ALTER TABLE MYTABLE ADD (
CONSTRAINT UK_MYTABLE
UNIQUE (ID,CATEGORY)
USING INDEX MYTABLE_IDX1
);

For a primary key constraint the syntax is similar.
In this example, an index named MYTABLE_PK will be automatically created on the fly:

ALTER TABLE MYTABLE ADD (
CONSTRAINT MYTABLE_PK
PRIMARY KEY (ID)
USING INDEX);

How to solve ORA-01031: insufficient privileges when creating a cross-schema fk constraint

Scenario:
you want to create a cross-schema Foreign key constraint:

 ALTER TABLE JIM.TRANSACTION
 ADD ( CONSTRAINT TRANSACTION_FK 
       FOREIGN KEY (TRANS_ID) REFERENCES 
       DWIGHT.TRANSACTION_HISTORY(TRANS_ID)
     );

In order to accomplish this, user JIM needs the REFERENCES system privilege on DWIGHT's table:

GRANT REFERENCES ON DWIGHT.TRANSACTION_HISTORY TO JIM;

How to fix ORA-02449: unique/primary keys in table referenced by foreign keys after an online redefinition

When attempting to drop the interim table after a successful online redefinition, you may get:

ORA-02449: unique/primary keys in table referenced by foreign keys

This is easy to overlook - the online redef procedure does not disable the foreign key relationship from other tables to your (now) obsolete interim table.

To find these tables and their constraints:

SELECT  TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
FROM    DBA_CONSTRAINTS
WHERE   OWNER = 'SH'
AND     CONSTRAINT_TYPE='R'
AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')

TABLE_NAME	CONSTRAINT_NAME	CONSTRAINT_TYPE	R_CONSTRAINT_NAME	STATUS
INVOICES	TMP$$_INVOICES_FK0	R	TMP$$_SALES_PK0	DISABLED
REFUNDS	TMP$$_REFUNDS_FK0	R	TMP$$_SALES_PK0	DISABLED
REPLENISHMENT	TMP$$_REPLENISHMENT_FK0	R	TMP$$_SALES_PK0	DISABLED

As can be seen from the table above, there are certainly constraints from other tables, pointing to the primary key on the interim table. They are disabled, but nevertheless preventing us from dropping the table.

Let's make sure that each of these "funny named" constraints have a sibling constraint, one that is named correctly after the redefintion:

SELECT TABLE_NAME,CONSTRAINT_NAME,R_CONSTRAINT_NAME,STATUS
FROM DBA_CONSTRAINTS 
WHERE TABLE_NAME in (
                    SELECT  TABLE_NAME
                    FROM    DBA_CONSTRAINTS
                    WHERE   OWNER = 'Sh'
                    AND     CONSTRAINT_TYPE='R'
                    AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')
)
AND CONSTRAINT_TYPE='R'
AND CONSTRAINT_NAME LIKE '%REPLENISHMENT%' OR CONSTRAINT_NAME LIKE '%INVOICES%' OR CONSTRAINT_NAME LIKE '%REFUNDS%'
ORDER BY TABLE_NAME,CONSTRAINT_NAME;

Result:

TABLE_NAME	CONSTRAINT_NAME	R_CONSTRAINT_NAME	STATUS
INVOICES	INVOICES_FK	SALES_PK	ENABLED
INVOICES	TMP$$_INVOICES_FK0	TMP$$_SALES_PK0	DISABLED
REFUNDS	REFUNDS_FK	SALES_PK	ENABLED
REFUNDS	TMP$$_REFUNDS_FK0	TMP$$_SALES_PK0	DISABLED
REPLENISHMENT	REPLENISHMENT_FK	SALES_PK	ENABLED
REPLENISHMENT	TMP$$_REPLENISHMENT_FK0	TMP$$_SALES_PK0	DISABLED

As you can see, the constraints come in pairs and the ones with names starting with TMP$$ are leftovers from the redefinition.

You can safely drop these constraints. Put the following in a .sql script and run it as sysdba:

alter session set nls_language='american';
set lines 200
set pages 0
set heading off
set feedback off
set trimspool on
set verify off
set echo off
spool 7.alter_table.sql

select 'spool 7.alter_table.log' from dual;

SELECT    'alter table '
        || B.OWNER
        || '.'
        || B.TABLE_NAME
        || ' drop constraint '
        || B.CONSTRAINT_NAME
        || ';'
  FROM DBA_CONSTRAINTS A
        FULL OUTER JOIN DBA_CONSTRAINTS B
           ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  WHERE A.OWNER = 'SH'
  AND A.TABLE_NAME='SALES_INTERIM'
  AND B.R_CONSTRAINT_NAME IS NOT NULL;
select 'exit' from dual;

exit

Run the script, and you will have a new script containing the drop-clauses:

alter table SH.INVOICES drop constraint TMP$$_INVOICES_FK0;
alter table SH.REFUNDS drop constraint TMP$$_REFUNDS_FK0;
alter table SH.REPLENISHMENT drop constraint TMP$$_REPLENISHMENT_FK0;

After these statements have been successfully executed, you can go ahead and drop the interim table.

The syntax for enabling and disabling a constraint

To enable a constraint:

ALTER TABLE mytable ENABLE CONSTRAINT mytable_c1;

or, if you wish to enable the constraint and validate its correctness at the same time:

ALTER TABLE mytable MODIFY CONSTRAINT mytable_c1 ENABLE VALIDATE;

Note that the following syntax is invalid and will throw ORA-00933: SQL command not properly ended:

ALTER TABLE mytable ENABLE CONSTRAINT mytable_c1 VALIDATE;

To disable a constraint:

ALTER TABLE mytable DISABLE CONSTRAINT mytable_c1;

or

ALTER TABLE mytable MODIFY CONSTRAINT mytable_c1 DISABLE;

How to add a check constraint to a table using ALTER TABLE

The following statement adds a check constraint to the table and allows Oracle to give it a system generated name:

ALTER TABLE MYTABLE ADD 
 CHECK (COL1 IS NOT NULL)
 ENABLE
 VALIDATE;

Note that when you execute the following DDL, Oracle will throw ORA-02438: Column check constraint cannot reference other columns:

ALTER TABLE MYTABLE ADD
  CONSTRAINT CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;

The above error only happens when you let Oracle give the constraint a system generated name.


If you are explisitly naming your constraint, the use of the CONSTRAINT keyword will work:

ALTER TABLE MYTABLE ADD
  CONSTRAINT mytable_cc2 CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;

How to use dbms_metadata to generate DDL for constraints

set long 10000
set heading off
set trimspool on
set lines 300
set longchunksize 300
spool all_mycons.sql
exec dbms_metadata.set_Transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

select dbms_metadata.get_ddl('CONSTRAINT','SYS_C006257121','SCOTT')  FROM DUAL;

-- For Foreign key constraints, use the REF_CONSTRAINT constant:
select dbms_metadata.get_ddl('REF_CONSTRAINT','MYFOREIGNKEY_FK','SCOTT')  FROM DUAL;

What is the difference between "in line" and "out of line" constraints?

The following examples create an "in line" constraint, since they both declare a constraint in the columns' definitions:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER PRIMARY KEY, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20)
);

An anonymous primary key constraint with a system generated name is created on the column PERSONELL_ID.
Optinally, give the constraint a specific name:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER CONSTRAINT STAFF_PK PRIMARY KEY, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20)
);

The following examples create an "out of line" constraint, since they are both declared separately from the columns' definitions:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20),
  PRIMARY KEY (PERSONELL_ID)
);

An anonymous primary key constraint with a system generated name is created on the column PERSONELL_ID.
Optinally, give the constraint a specific name:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20),
  CONSTRAINT STAFF_PK PRIMARY KEY (PERSONELL_ID)
);

The "ALTER TABLE" equivalent of the "in line" syntax is:

ALTER TABLE STAFF 
MODIFY PERSONELL_ID PRIMARY KEY;

Or with explicitly named constraint:

ALTER TABLE STAFF 
MODIFY PERSONELL_ID CONSTRAINT STAFF_ID PRIMARY KEY;

The "ALTER TABLE" equivalent of the "out of line" syntax is:

ALTER TABLE STAFF
ADD PRIMARY KEY (PERSONELL_ID);

Or with explicitly named constraint:

ALTER TABLE STAFF
ADD CONSTRAINT STAFF_PK PRIMARY KEY(PERSONELL_ID);

Note that NOT NULL constraints cannot be created "out of line".

How does Oracle Data Pump Import handles referential integrety constraint violations?

How does Oracle Data Pump Import handles referential integrety constraint violations?

To find out, let's use the two tables SHIPS and PORTS, which are connected by a referential constraint from SHIPS to PORTS, called FK_SHIPS_PORTS.

CREATE TABLE SHIPS
(
  SHIP_ID       NUMBER,
  SHIP_NAME     VARCHAR2(20 BYTE),
  CAPACITY      NUMBER,
  LENGTH        NUMBER,
  HOME_PORT_ID  NUMBER,
  LIFEBOATS     NUMBER(3),
  CONSTRAINT PK_SHIPS PRIMARY KEY (SHIP_ID),
  CONSTRAINT FK_SHIPS_PORTS FOREIGN KEY (HOME_PORT_ID) 
  REFERENCES PORTS (PORT_ID)
);

CREATE TABLE PORTS
(
  PORT_ID    NUMBER,
  PORT_NAME  VARCHAR2(20 BYTE),
  COUNTRY    VARCHAR2(40 BYTE),
  CAPACITY   NUMBER,
  CONSTRAINT PK_PORTS PRIMARY KEY (PORT_ID)
);

In other words, you cannot add a ship without a valid PORT_ID that already exist in table PORTS.

PORT_ID	PORT_NAME	COUNTRY	CAPACITY
1	Baltimore	USA
2	Charleston	USA
3	Tampa	USA
4	Miami	USA

SHIP_ID	SHIP_NAME	CAPACITY	LENGTH	HOME_PORT_ID	LIFEBOATS
1	Codd Crystal	2052	855	1	80
15	Codd Champion	2000	650		30
2	Codd Elegance	2974	952	2	95
16	Codd Victorious	2055	876	2
17	Codd Grandeur	2030	840	4
18	Codd Prince	1500	550	2	32
20	Codd Norway	1500	900	3	80

I now remove one row from the PORTS table, so that new rows being imported will have a missing parent key. In order to do that I need to also remove any child record from SHIPS, in my case, only one:

DELETE TESTUSER.SHIPS WHERE HOME_PORT_ID = 1;
DELETE TESTUSER.PORTS WHERE PORT_ID = 1;
COMMIT;

1 row deleted.
1 row deleted.
Commit complete.

SCENARIO 1: "What happens if we use TABLE_EXISTS_ACTION=REPLACE when the table being replaced has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• All rows are imported into SHIPS, including the offending ones
• The referential constraint is dropped.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (TESTUSER.FK_SHIPS_PORTS) - parent keys not found
Failing sql is:
ALTER TABLE "TESTUSER"."SHIPS" ADD CONSTRAINT "FK_SHIPS_PORTS" FOREIGN KEY ("HOME_PORT_ID") REFERENCES "TESTUSER"."PORTS" ("PORT_ID") DEFERRABLE ENABLE


SCENARIO 2: "What happens if we use TABLE_EXISTS_ACTION=TRUNCATE when the table being truncated has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• The SHIPS table is left truncated, no rows are loaded.
• The referential constraint stays.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TESTUSER"."SHIPS" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (TESTUSER.FK_SHIPS_PORTS) violated - parent key not found


Both cases requires manual intervention afterwards to cleanup offending rows, and then to recreate the constraint.
One way to do so would be to allow for orphan rows through temporarily disabling the index, loading the rows, then reenabling the index using the NOVALIDATE option.

First disable the constraint:
SQL> ALTER TABLE SHIPS DISABLE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

Load the data once more:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Finally, enable the constraint again. However, tell oracle to disregard existing rows, only apply the constraint to new rows:
SQL> ALTER TABLE SHIPS ENABLE NOVALIDATE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

SQL>

Example is based on Steve O'Heam's "SQL Certified SQL Expert Exam Guide"

SQL statement to find all referential integrity constraints that relates to a specific table

Here is how to use a self-join of the view USER_CONSTRAINTS to find constraints that is referring to a specific table. Note that you can also use the DBA_CONSTRAINTS if logged in as a privileged user.

First, set up a couple of extra tables in the SCOTT schema to illustrate the point (the existing SCOTT objects will contain only one - 1 - Foreign Key by default):

CONNECT SCOTT/TIGER
CREATE TABLE PORTS
 (
    PORT_ID     NUMBER,
    PORT_NAME   VARCHAR2 (20),
    COUNTRY     VARCHAR2 (40),
    CAPACITY    NUMBER
 );


 CREATE TABLE SHIPS
 (
    SHIP_ID        NUMBER,
    SHIP_NAME      VARCHAR2 (20),
    CAPACITY       NUMBER,
    LENGTH         NUMBER,
    HOME_PORT_ID   NUMBER
 );

 ALTER TABLE SHIPS
 ADD CONSTRAINT PK_SHIPS PRIMARY KEY (SHIP_ID);

 ALTER TABLE PORTS
 ADD CONSTRAINT PK_PORTS PRIMARY KEY (PORT_ID);

 ALTER TABLE SHIPS
 ADD CONSTRAINT SHIPS_PORTS_FK FOREIGN KEY (HOME_PORT_ID)
 REFERENCES PORTS (PORT_ID);

Now, let's find the relationships between the Foreign key constraints and their corresponding unique key constraint:

CONNECT SCOTT/TIGER
SET LINES 200
SET HEADING OFF
SET FEEDBACK OFF
SET PAGES 0
SELECT    B.CONSTRAINT_NAME
        || ' on table '
        || B.TABLE_NAME
        || ' points to table '
        || A.TABLE_NAME
           "Relationship"
   FROM USER_CONSTRAINTS A
        FULL OUTER JOIN USER_CONSTRAINTS B
           ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  WHERE A.OWNER = 'SCOTT' AND B.R_CONSTRAINT_NAME IS NOT NULL;

Here you can see the relationships between the constraints and their tables:

Relationship
SHIPS_PORTS_FK on table SHIPS points to table PORTS
FK_DEPTNO on table EMP points to table DEPT

In my case I wanted to drop the constraints, so I concatenated the columns with strings to generate a "drop constraint" script:

CONNECT SCOTT/TIGER

 SELECT    'alter table '
        || B.OWNER
        || '.'
        || B.TABLE_NAME
        || ' drop constraint '
        || B.CONSTRAINT_NAME
        || ';'
  FROM USER_CONSTRAINTS A
        FULL OUTER JOIN USER_CONSTRAINTS B
           ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  WHERE A.OWNER = 'SCOTT' AND B.R_CONSTRAINT_NAME IS NOT NULL;

Returned rows:

alter table SCOTT.SHIPS drop constraint SHIPS_PORTS_FK;
 alter table SCOTT.EMP drop constraint FK_DEPTNO;

Note that you can achieve the same goal by using a subquery instead of a FULL OUTER JOIN:

CONNECT SCOTT/TIGER
 SELECT OWNER,TABLE_NAME,CONSTRAINT_NAME
 FROM  USER_CONSTRAINTS
WHERE  R_CONSTRAINT_NAME IN (
             SELECT CONSTRAINT_NAME
            FROM USER_CONSTRAINTS
             WHERE CONSTRAINT_TYPE='P'
            AND OWNER = 'SCOTT'
 );

Output:

OWNER	TABLE_NAME	CONSTRAINT_NAME
SCOTT	EMP	FK_DEPTNO
SCOTT	SHIPS	SHIPS_PORTS_FK