Showing posts with label Constraints and data integrity. Show all posts
Showing posts with label Constraints and data integrity. Show all posts
Friday, May 23, 2025

How to find constraints on a table in postgres

›
SELECT con.conname AS constraint_name, CASE con.contype WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u...
Tuesday, October 26, 2021

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_h...
Wednesday, May 12, 2021

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 scri...
Tuesday, April 9, 2019

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: ...
Tuesday, February 12, 2019

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 ...
Thursday, June 28, 2018

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 r...
Thursday, March 16, 2017

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 correc...
Wednesday, March 15, 2017

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 ...
Monday, January 18, 2016

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_pa...
Tuesday, March 18, 2014

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 : C...
Thursday, January 16, 2014

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 ...
Thursday, December 12, 2013

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 a...