Add Foreign Key Relationship Between data_stream_sequence and data_stream_ids

[yuanchen233]

Description

We need to establish a proper foreign key relationship between the data_stream_sequence and data_stream_ids tables in the database. This change will improve data integrity and ensure that each data_stream_id in the data_stream_sequence table corresponds to a valid id in the data_stream_ids table.

Tasks

1. Schema Changes:

   • Add a foreign key constraint from data_stream_sequence.data_stream_id to data_stream_ids.id.
   • Create an index on the data_stream_id column in the data_stream_sequence table for better query performance.

2. Data Validation:

   • Identify and handle orphaned data_stream_id values in the data_stream_sequence table (if any).
   • Options for handling orphaned data:
     • Delete rows with invalid data_stream_id values.
     • Set invalid data_stream_id values to NULL (if the relationship is optional).

3. Update Code:

   • Modify JPA entities to include the relationship:
     • Add @ManyToOne mapping in the DataStreamSequence entity.
     • Optionally add a @OneToMany reverse mapping in the DataStreamId entity.

4. Test and Deploy:

   • Create Flyway migration script to manage schema changes.
   • Test migration on a staging environment with real data before deployment to production.

Expected Outcomes

• Ensure that all data_stream_id values in data_stream_sequence are valid.
• Maintain referential integrity between the data_stream_sequence and data_stream_ids tables.
• Improve query performance involving data_stream_id.

References

Current DataStreamSequence Entity

@Entity(name = "data_stream_sequence")
data class DataStreamSequence(
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Int = 0,
    val dataStreamId: Int? = 0, // Foreign Key to be added
    ...
)

[yuanchen233]

potential migration script:

-- Step 1: Ensure all data_stream_id values in data_stream_sequence are valid
DELETE FROM data_stream_sequence
WHERE data_stream_id IS NOT NULL
  AND data_stream_id NOT IN (SELECT id FROM data_stream_ids);

-- Step 2: Add the foreign key constraint
ALTER TABLE data_stream_sequence

ADD CONSTRAINT fk_data_stream
FOREIGN KEY (data_stream_id)
REFERENCES data_stream_ids (id)
ON DELETE CASCADE;

-- Step 3: Create an index on data_stream_id for better query performance
CREATE INDEX idx_data_stream_id ON data_stream_sequence (data_stream_id);