[Diagrams] How to Write Oracle Foreign Keys & Check Constraints and Their Differences | Introduction to Data Integrity

Have you ever been chased by system bugs caused by invalid data registration, such as “orders for non-existent customers” or “negative prices”?

In Oracle Database, the last line of defense for ensuring data quality (integrity) is the “Constraint”. In particular, Oracle Foreign Keys and Check Constraints are the foundation of table design, but failing to understand them correctly can lead to errors and performance degradation.

This article explains the differences between these two constraints, how to write them correctly, and points to note during operation, using easy-to-understand diagrams and working SQL examples.

Conclusion: Distinguishing Between Foreign Key and Check Constraints (To-Do List)
1. What is a Foreign Key Constraint?
2. What is a Check Constraint?
3. Differences Between Foreign Key and Check Constraints
4. Troubleshooting (Common Errors)
5. Operational/Security Notes and Best Practices
FAQ: Frequently Asked Questions
Summary

Conclusion: Distinguishing Between Foreign Key and Check Constraints (To-Do List)

For those in a hurry, here is a summary of the key points for using constraints.

Foreign Key Constraint (FK)

Purpose: “Allow only values that exist in the Parent table (Master).”
Usage: Linking Customer IDs, Product IDs, etc.
Mandatory Setting: Create an index on the FK column of the child table (to prevent lock contention).

Check Constraint (Check)

Purpose: “Guarantee that values meet specific conditions (range or format).”
Usage: Price is a positive number, status is a valid value (0 or 1), etc.
Note: Complex business logic should be controlled on the application side for easier modification.

1. What is a Foreign Key Constraint?

A Foreign Key constraint is a mechanism that guarantees that the value of a column in one table (Child) actually exists in the Primary Key or Unique Key of another table (Parent). This maintains “Referential Integrity,” which is the core of Relational Databases (RDB).

Features and Mechanism (Diagram)

The figure below shows the relationship between Customers (Parent) and Orders (Child).

The Foreign Key enforces “reference from Child to Parent” and controls how child data is handled when parent data is deleted (e.g., cascading delete).

       [Parent Table: Customers]                 [Child Table: Orders]
    +-----------------------------+           +-----------------------------+
    | COLUMN NAME   | TYPE & KEY  |           | COLUMN NAME   | TYPE & KEY  |
    +---------------+-------------+           +---------------+-------------+
    | customer_id   | NUMBER (PK) |<----(*)-- | customer_id   | NUMBER (FK) |
    | customer_name | VARCHAR2    | References| order_id      | NUMBER (PK) |
    +-------+---------------------+           | order_date    | DATE        |
            |                                 +-----------------------------+
            |
            |  ★ In the case of ON DELETE CASCADE
            +-------------------------------------> If Parent (ID=1) is deleted...
                                                    Child (ID=1) is automatically deleted

    (*) Creating an index here is a best practice

Referential Integrity: IDs not present in the parent (e.g., 999) cannot be registered in the child (ORA-02291 error).
Cascading Delete: You can define behavior when parent data is deleted (such as ON DELETE CASCADE).

Implementation and Execution Example (Oracle SQL)

Here, we will create tables according to the structure in the diagram.

Prerequisites:

The executing user must have CREATE TABLE privileges.
Create the Parent table first.

-- 1. Create parent table (customers)
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100)
);

-- 2. Create child table (orders)
-- ON DELETE CASCADE: Setting to automatically delete child if parent is deleted
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_date DATE,
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- 3. Register normal data
INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Tanaka');
INSERT INTO orders (order_id, customer_id, order_date) VALUES (101, 1, SYSDATE);
COMMIT;

SQL> SELECT * FROM customers;

CUSTOMER_ID CUSTOMER_NAME
----------- ---------------------------------
          1 Tanaka

SQL> SELECT * FROM orders;

  ORDER_ID CUSTOMER_ID ORDER_DAT
---------- ----------- ---------
       101           1 30-DEC-25

Point: It is recommended to explicitly name the constraint, such as CONSTRAINT fk_customer. This allows for immediate identification of which constraint was violated when an error occurs.

Operation Check: Errors and Cascading Delete

We will intentionally perform operations that break integrity to confirm how Oracle protects the data.

Case 1: Specifying a non-existent Parent ID

INSERT INTO orders (order_id, customer_id, order_date) VALUES (102, 999, SYSDATE);

Result:

SQL> INSERT INTO orders (order_id, customer_id, order_date) VALUES (102, 999, SYSDATE);
INSERT INTO orders (order_id, customer_id, order_date) VALUES (102, 999, SYSDATE)
*
ERROR at line 1:
ORA-02291: integrity constraint (USER1.FK_CUSTOMER) violated - parent key not found

ORA-02291: integrity constraint (…) violated – parent key not found

→ Blocked because Customer ID 999 does not exist in the customers table.

Case 2: Deleting Parent Data (Cascade Delete)

DELETE FROM customers WHERE customer_id = 1;

-- Verification
SELECT * FROM orders WHERE customer_id = 1;

Result:

SQL> DELETE FROM customers WHERE customer_id = 1;

1 row deleted.

SQL> SELECT * FROM orders WHERE customer_id = 1;

no rows selected

no rows selected

→ Due to ON DELETE CASCADE, the related order data was also automatically deleted.

2. What is a Check Constraint?

A Check Constraint is a function that forces values stored in a column to satisfy a specific condition formula (True). It prevents logically impossible data from entering, such as “negative prices” or “birthdates in the future.”

Features and Mechanism (Diagram)

Imagine the Check Constraint as a “Gatekeeper” standing at the entrance of the data.

             [Table: Products]
    +-----------------------------------------+
    | product_id  (PK)                        |
    +-----------------------------------------+
    | product_name                            |
    +-----------------------------------------+
    | price                                   |
    |   +---------------------------------+   |
    |   | [Gatekeeper] CHECK (price > 0)  |   |
    |   |  - Input: 1000  -> OK (Pass)    |   |
    |   |  - Input: -500  -> NG (Block)   |   |
    |   +---------------------------------+   |
    +-----------------------------------------+
    | stock_quantity                          |
    |   +---------------------------------+   |
    |   | [Gatekeeper] CHECK (stock >= 0) |   |
    |   +---------------------------------+   |
    +-----------------------------------------+

Flexibility: You can write conditions similar to the SQL WHERE clause, such as > 0 or IN ('A', 'B').
Handling NULL: If the result of the condition formula is NULL (when no value is entered), Oracle considers it “not violated” and lets it pass (this is a common pitfall).

Implementation and Execution Example

We will control the products table so that the price and stock quantity do not become negative.

-- Create table
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    price NUMBER CONSTRAINT chk_price_pos CHECK (price > 0),
    stock_quantity NUMBER CONSTRAINT chk_stock_min CHECK (stock_quantity >= 0)
);

-- Register normal data
INSERT INTO products (product_id, product_name, price, stock_quantity)
VALUES (1, 'Laptop', 100000, 10);
COMMIT;

SQL> SELECT * FROM products;

PRODUCT_ID PRODUCT_NA      PRICE STOCK_QUANTITY
---------- ---------- ---------- --------------
         1 Laptop         100000             10

Operation Check: Inserting Violated Data

INSERT INTO products (product_id, product_name, price, stock_quantity)
VALUES (2, 'Smartphone', -50000, 5);

Result:

SQL> INSERT INTO products (product_id, product_name, price, stock_quantity)
  2  VALUES (2, 'Smartphone', -50000, 5);
INSERT INTO products (product_id, product_name, price, stock_quantity)
*
ERROR at line 1:
ORA-02290: check constraint (USER1.CHK_PRICE_POS) violated

ORA-02290: check constraint (…) violated

→ Blocked because it does not meet the price > 0 condition.

Application: Check Constraint Across Multiple Columns

You can also define conditions that use multiple columns within the same row, such as “The sum of base salary and bonus must not exceed a certain amount.”

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    salary NUMBER,
    bonus NUMBER,
    CONSTRAINT chk_total_comp CHECK (salary + bonus <= 100000)
);

3. Differences Between Foreign Key and Check Constraints

Item	Foreign Key Constraint (FK)	Check Constraint (Check)
Purpose	Maintaining integrity between tables	Maintaining validity of the value itself
Reference Destination	Column in another table (Parent)	Column within the same table
Verification Content	“Does it exist in the parent?”	“Does it satisfy the condition formula?”
Main Usage	Master reference, Parent-Child relationship	Amount range, Category values, Format check
Handling NULL	Allowed (unless there is a NOT NULL constraint)	Allowed (since condition evaluation becomes UNKNOWN)

4. Troubleshooting (Common Errors)

These are representative ORA errors that occur when constraints are set, and how to deal with them.

Error Code	Message Summary	Cause	Solution
ORA-02291	parent key not found	The FK value tried to register in the child table does not exist in the parent table.	Register the corresponding data in the parent table or correct the input value.
ORA-02292	child record found	Tried to delete parent table data, but child data using that ID exists.	Delete child data first, or consider `ON DELETE CASCADE` when defining the FK.
ORA-02290	check constraint violated	Tried to register/update a value that does not satisfy the CHECK condition.	Confirm if the input data matches the business rules (condition formula).

5. Operational/Security Notes and Best Practices

To ensure stable operation of Oracle Database, please pay attention to the following points.

1. Always Index Foreign Key Columns

This is the most important item for performance.

If there is no index on the Foreign Key column, updating or deleting the Primary Key of the parent table may cause a table lock on the entire child table. Since this causes delays in the entire system, make it a habit to create an index on the FK column.

-- Example of creating an index on an FK column
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

2. Be Careful with Excessive Business Logic

Check constraints are powerful, but if you put too complex rules (e.g., amount checks including consumption tax calculation) on the DB side, modification becomes difficult when specifications change.

It is recommended to separate usage as follows:

Universal rules (Price is a positive number, date is valid) → DB Constraints
Changeable rules (Application of campaign prices, etc.) → Application

3. Temporary Disabling During Data Load

When loading large amounts of data, constraint checks can become a bottleneck for processing time. You may be able to shorten the time by temporarily setting constraints to DISABLE (disable) and then ENABLE (enable/verify) after loading.

Risk: Invalid data may enter while disabled. Please perform this carefully in production environments.

FAQ: Frequently Asked Questions

Q1. Does adding a Foreign Key constraint make it slower?

A. Because a reference check of the parent table runs during insertion/update, there is a very slight overhead compared to having no constraints. However, this is negligible compared to the cost of fixing data inconsistencies later. As mentioned earlier, please avoid lock problems by creating indexes on FK columns.

Q2. Can I add constraints to a table that already contains data?

A. Yes, it is possible. However, if existing data violates the constraint, ALTER TABLE … ADD CONSTRAINT will fail. In that case, you need to correct the invalid data first or use the NOVALIDATE option to set it to “check only new data”.

Q3. I cannot delete (DROP) the parent table.

A. A parent table referenced by a child table cannot be deleted (ORA-02449). You can force deletion including constraints by adding an option like DROP TABLE customers CASCADE CONSTRAINTS;, but be careful as the scope of impact is wide.

Summary

Oracle Foreign Key and Check Constraints are basic features for protecting data quality.

Foreign Keys guarantee linking with master data and prevent orphan data.
Check Constraints prevent the intrusion of abnormal values and ensure minimum data quality on the DB side.
Create indexes on Foreign Key columns to prevent lock contention.
When errors occur, check ORA-02291 (Parent missing) or ORA-02290 (Check violation).

Implementation in SQL is easy once you learn it. We recommend that you first create tables in a development environment, intentionally trigger errors, and confirm the behavior.

[reference]
Oracle Database SQL Language Reference, 19c

This article explains based on Oracle Database 19c (screens and default values may differ for other versions).