Update Primary Key on channel session Table

⚠️ Prerequisites

CX Version - CX4.9.3
Ensure you have appropriate database access (e.g., db_owner or similar).
Make sure no ongoing inserts/updates are happening on channel_session during this operation to avoid conflicts.
Ideally, take a backup or ensure the environment has point-in-time recovery enabled.

✅ 1. MYSQL: Step-by-Step Guide

🔹 Step 1: Drop and Add New Composite Primary Key

CODE

ALTER TABLE channel_session DROP PRIMARY KEY;
ALTER TABLE channel_session ADD PRIMARY KEY (conversation_id, session_id);

Script is also available at: kubernetes/pre-deployment/reportingConnector/dbScripts/dbupdate/historical_reports_db_update_script_MYSQL.sql.sql

🔹 Step 2: Verify

Check that the new primary key is on (conversation_id, session_id).

CODE

 SHOW KEYS FROM channel_session WHERE Key_name = 'PRIMARY';

✅ 2. MSSQL (SQL Server): Step-by-Step Guide

🔹 Step 1: Find Existing Primary Key Constraint Name

Run:

CODE

SELECT name 
FROM sys.key_constraints 
WHERE type = 'PK' AND parent_object_id = OBJECT_ID('channel_session');

Note down the result (e.g., PK__channel__abc123).

Script is also available at : kubernetes/pre-deployment/reportingConnector/dbScripts/dbupdate/get_primary_key_name_channel_session.sql

🔹 Step 2: Drop the Primary Key

Replace with the actual name from Step 1:

CODE

ALTER TABLE channel_session DROP CONSTRAINT <replce with primary key name noted from step 1>;

🔹 Step 3: Add New Composite Primary Key

CODE

ALTER TABLE channel_session
ADD CONSTRAINT PK_channel_session
PRIMARY KEY CLUSTERED (conversation_id ASC, session_id ASC)
WITH (
	PAD_INDEX = OFF,
	STATISTICS_NORECOMPUTE = OFF,
	IGNORE_DUP_KEY = OFF,
	ALLOW_ROW_LOCKS = ON,
	ALLOW_PAGE_LOCKS = ON,
	OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
) ON [PRIMARY];

Script is also available at : kubernetes/pre-deployment/reportingConnector/dbScripts/dbupdate/historical_reports_db_update_script_MSSQL.sql.sql

🔹 Step 4: Verify

Check that the new primary key is on (conversation_id, session_id).

CODE

SELECT 
    KU.TABLE_NAME, 
    KU.COLUMN_NAME, 
    KU.CONSTRAINT_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
    ON TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
WHERE 
    TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND KU.TABLE_NAME = 'channel_session';