Oracle新手咨询：ETL测试中表约束的验证方法及示例查询

Hey there! Since you're new to Oracle and diving into ETL testing, validating table constraints for T1 and T2 is a key step to ensure your data stays consistent and accurate after the ETL process. Let’s walk through the most common constraint checks with practical query examples tailored to your use case.

Primary keys are the backbone of data integrity—they guarantee every row is unique and non-null. After ETL, you’ll want to make sure no duplicates slipped in and that the constraint itself is intact.

Check for duplicate primary key values #

Suppose t1_id is the primary key of T1. This query will flag any duplicate entries:

SELECT t1_id, COUNT(*) AS duplicate_count
FROM T1
GROUP BY t1_id
HAVING COUNT(*) > 1;

If this returns results, your ETL process has a duplicate key issue that needs fixing right away.

View primary key constraint definitions for T1/T2 #

To confirm the constraint exists and see which columns it applies to:

SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name IN ('T1', 'T2')
  AND constraint_type = 'P'
ORDER BY table_name, position;

If T2 has a foreign key referencing T1 (say t1_fk linking to T1’s t1_id), you need to ensure there are no "orphan" records in T2 that don’t have a matching entry in T1.

Check for orphaned foreign key records #

SELECT t2.*
FROM T2
LEFT JOIN T1 ON T2.t1_fk = T1.t1_id
WHERE T1.t1_id IS NULL;

Any rows returned here mean T2 has records pointing to a non-existent row in T1—this breaks referential integrity.

View foreign key constraint details #

SELECT 
  c.constraint_name,
  cc.column_name,
  c.r_constraint_name AS referenced_pk_constraint,
  cr.table_name AS referenced_table
FROM user_constraints c
JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
JOIN user_constraints cr ON c.r_constraint_name = cr.constraint_name
WHERE c.table_name IN ('T1', 'T2')
  AND c.constraint_type = 'R'
ORDER BY c.table_name;

This shows you exactly which foreign keys exist, their target columns, and which primary keys they reference.

Unique constraints ensure columns (or column combinations) have no duplicates (unlike primary keys, they can sometimes allow NULLs, depending on setup).

Check for duplicate values in a unique column #

If T1 has a unique constraint on the email column:

SELECT email, COUNT(*) AS duplicate_count
FROM T1
GROUP BY email
HAVING COUNT(*) > 1;

View unique constraint definitions #

SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name IN ('T1', 'T2')
  AND constraint_type = 'U'
ORDER BY table_name, position;

Non-null constraints prevent specific columns from having NULL values. ETL processes can accidentally introduce NULLs where they shouldn’t be, so this check is crucial.

Check for NULL values in non-null columns #

For T1’s customer_name column (which should never be NULL):

SELECT *
FROM T1
WHERE customer_name IS NULL;

View non-null constraint details #

SELECT column_name
FROM user_tab_columns
WHERE table_name IN ('T1', 'T2')
  AND nullable = 'N';

This lists all columns in T1 and T2 that are marked as non-nullable.

Check constraints enforce business rules (e.g., age >= 18, status IN ('ACTIVE', 'INACTIVE')). You’ll want to verify no ETL records violate these rules.

Check for check constraint violations #

Suppose T2 has a check constraint that order_amount > 0:

SELECT *
FROM T2
WHERE order_amount <= 0;

Any rows here violate the check constraint and need to be investigated.

View check constraint definitions #

SELECT constraint_name, search_condition
FROM user_constraints
WHERE table_name IN ('T1', 'T2')
  AND constraint_type = 'C'
  AND constraint_name NOT LIKE '%SYS_%'; -- Exclude system-generated NOT NULL constraints

This shows you the actual business rules enforced by check constraints on your tables.

内容的提问来源于stack exchange，提问作者lifeofpy