How Do I Link the State table to the People table

In Oracle APEX, building relationships between tables is a key part of effective data modeling and application development. One common scenario is linking a State table to a People table, allowing each person to be associated with a specific state. This is typically done using a foreign key constraint, which not only enforces referential integrity but also enables dynamic LOVs for form fields, making data entry easier and more consistent. In this blog, we’ll walk through how to create this link step by step, covering table structure, constraint setup, and how to reflect this relationship in your APEX forms.

To link the State table to the People table in Oracle APEX, you need to follow standard relational database principles. This involves creating a foreign key relationship between the People table and the State table, ensuring that each person record can reference a valid state. This link not only enforces data integrity but also allows you to implement dynamic LOVs (List of Values) in APEX forms and reports, making data entry more efficient and error-free.

Step 1: Table Design

Make sure you have two tables: STATES and PEOPLE.

CREATE TABLE STATES (
    STATE_CODE VARCHAR2(2) PRIMARY KEY,
    STATE_NAME VARCHAR2(100)
);

CREATE TABLE PEOPLE (
    PERSON_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    FIRST_NAME VARCHAR2(100),
    LAST_NAME VARCHAR2(100),
    STATE_CODE VARCHAR2(2),
    CONSTRAINT fk_people_state FOREIGN KEY (STATE_CODE) REFERENCES STATES (STATE_CODE)
);

In this example:

• STATE_CODE is the primary key of the STATES table.

• STATE_CODE in the PEOPLE table is a foreign key that references the STATES table.

Step 2: Populate the States Table

Insert the list of U.S. states (or any relevant data) into the STATES table.

INSERT INTO STATES (STATE_CODE, STATE_NAME) VALUES ('CA', 'California');
INSERT INTO STATES (STATE_CODE, STATE_NAME) VALUES ('TX', 'Texas');
INSERT INTO STATES (STATE_CODE, STATE_NAME) VALUES ('NY', 'New York');
-- Add more states as needed
COMMIT;

Step 3: Create a Form in APEX

1. In Oracle APEX, go to App Builder.

2. Open your application or create a new one.

3. From the Create menu, select Form → Form on a Table with Report.

4. Choose the PEOPLE table.

5. During the wizard, APEX will detect STATE_CODE and allow you to configure it as a Select List.

6. For the LOV source, select Table/View and choose STATES.

7. Set Display Value as STATE_NAME and Return Value as STATE_CODE.

This LOV will now show the full state name while storing the two-letter code.

Step 4: Using LOV in Existing Forms

If you already have a form:

1. Go to the Page Designer.

2. Find the STATE_CODE item.

3. Change the Type to Select List.

4. Set List of Values to:

   • Type: SQL Query

   • SQL Query:

SELECT STATE_NAME d, STATE_CODE r FROM STATES ORDER BY STATE_NAME

This tells APEX to display the full state name (d) and return the code (r) when a user selects a value.

Step 5: Testing the Setup

Run the application and navigate to your People form. The State field should now appear as a drop-down list populated with data from the STATES table. When you create or update a person, APEX will ensure that the selected state exists in the STATES table.

This approach provides a scalable and consistent way to manage relationships between tables in Oracle APEX, making your applications more robust and user-friendly.

Example

Linking the State table to the People table ensures your application maintains clean, relational data and improves usability by enabling drop-down lists for state selection. By using a foreign key and leveraging APEX form capabilities, you create a user-friendly interface that enforces valid data entry and supports future scalability. With this setup, your application becomes more maintainable and aligned with relational database best practices.