How to check all the Bamboo database table constraints
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
Summary
This page covers database queries to extract all the constraints on Bamboo database tables.
This is usually helpful in scenarios where we see some database failures on Bamboo logs where it says unique key or Integrity constraint violated and prints the constraint name, the below queries would be helpful to check the details related to the constraint like tables and columns involved which will help to investigate the problem area properly
[DelayedDeletionThread] [SqlExceptionHelper] ORA-02292: integrity constraint (BAMBOOAPP.FK_6KUY829K0Q2WB34WHK0GBHRS9) violated - child record foundEnvironment
Bamboo connected to an external database.
Solution
Postgres
SELECT PGC.CONNAME AS constraint_name,
CCU.TABLE_SCHEMA AS table_schema,
CCU.TABLE_NAME,
CCU.COLUMN_NAME,
CONTYPE,
PG_GET_CONSTRAINTDEF(PGC.OID)
FROM PG_CONSTRAINT PGC
JOIN PG_NAMESPACE NSP
ON NSP.OID = PGC.CONNAMESPACE
JOIN PG_CLASS CLS
ON PGC.CONRELID = CLS.OID
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON PGC.CONNAME = CCU.CONSTRAINT_NAME
AND NSP.NSPNAME = CCU.CONSTRAINT_SCHEMA
ORDER BY CCU.TABLE_NAME; Oracle
SELECT CONS.OWNER AS CHILD_OWNER,
CONS.TABLE_NAME AS CHILD_TABLE,
CONS.CONSTRAINT_NAME AS CONSTAINT_NAME,
CONS.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
COL.OWNER AS PARENT_OWNER,
COL.TABLE_NAME AS PARENT_TABLE,
COL.COLUMN_NAME AS COLUMN_NAME
FROM DBA_CONS_COLUMNS COL,
DBA_CONSTRAINTS CONS
WHERE CONS.R_OWNER = COL.OWNER
AND CONS.R_CONSTRAINT_NAME = COL.CONSTRAINT_NAME
AND CONS.TABLE_NAME = 'yourTableName'; MySQL
SELECT TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_COLUMN_NAME,
REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'yourTableName'; Please replace yourTableName with the Bamboo DB table name for which you are checking the constraint for example build