Unique Constraints for School Records

Overview

This implementation prevents duplicate school records by enforcing:

• At least ONE of these fields must be provided: school_recognition_no, general_register_no, or affiliation_no
• Whichever field(s) are provided must be unique across all schools
• Each field individually enforces uniqueness (when provided)

Fields:

• school_recognition_no - School recognition number (must be unique if provided)
• general_register_no - General register number (must be unique if provided)
• affiliation_no - Affiliation number (must be unique if provided)

---

Implementation Details

1. Database Level (Unique Constraints)

Unique indexes have been added to enforce uniqueness at the database level:

-- school_recognition_no must be unique (allows NULL)
CREATE UNIQUE INDEX idx_schools_recognition_no_unique 
ON schools(school_recognition_no) 
WHERE school_recognition_no IS NOT NULL;

-- general_register_no must be unique (allows NULL)
CREATE UNIQUE INDEX idx_schools_general_register_no_unique 
ON schools(general_register_no) 
WHERE general_register_no IS NOT NULL;

-- affiliation_no must be unique (allows NULL)
CREATE UNIQUE INDEX idx_schools_affiliation_no_unique 
ON schools(affiliation_no) 
WHERE affiliation_no IS NOT NULL;

Note: NULL values are allowed (multiple NULLs won't conflict), but non-NULL values must be unique.

---

2. Application Level (Validation)

The School model now includes:

• checkForDuplicates() - Checks for duplicates before insert/update
• Pre-validation in create() and update() methods
• Clear error messages identifying which field(s) have duplicates

---

3. API Error Response

When a duplicate is detected, the API returns:

{
  "success": false,
  "error": "Duplicate record found",
  "duplicates": [
    {
      "field": "school_recognition_no",
      "value": "4260/37",
      "message": "School recognition number already exists"
    }
  ]
}

HTTP Status: 409 Conflict

---

Running the Migration

Step 1: Check for Existing Duplicates

The migration script will automatically check for existing duplicates:

npm run migrate-unique

If duplicates exist, the script will:

• List all duplicate values
• Show which records have duplicates
• Stop the migration (you must resolve duplicates first)

Step 2: Resolve Duplicates (if any)

If duplicates are found, you need to:

1. Review duplicate records:

   -- Check duplicates
   SELECT school_recognition_no, COUNT(*) as cnt, array_agg(id) as ids
   FROM schools
   WHERE school_recognition_no IS NOT NULL
   GROUP BY school_recognition_no
   HAVING COUNT(*) > 1;
   

2. Update or delete duplicate records:

   -- Update duplicate to a unique value
   UPDATE schools 
   SET school_recognition_no = 'NEW_VALUE' 
   WHERE id = <duplicate_id>;
   
   -- OR delete if it's truly a duplicate
   DELETE FROM schools WHERE id = <duplicate_id>;
   

3. Re-run the migration:

   npm run migrate-unique
   

Step 3: Verify Constraints

After migration, test the constraints:

-- Try to insert a duplicate (should fail)
INSERT INTO schools (name, district, school_recognition_no)
VALUES ('Test School', 'Test District', 'EXISTING_VALUE');
-- Error: duplicate key value violates unique constraint

---

Testing

Test 0: Create School Without Any Identifier (Should Fail)

curl -X POST http://localhost:3000/api/schools \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Test School",
    "district": "Test"
  }'

Expected Response (400):

{
  "success": false,
  "error": "At least one identifier is required",
  "validationErrors": [
    {
      "field": "identifier",
      "message": "At least one of school_recognition_no, general_register_no, or affiliation_no must be provided"
    }
  ]
}

Test 1: Create School with Duplicate Recognition Number

curl -X POST http://localhost:3000/api/schools \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Test School",
    "district": "Test",
    "schoolRecognitionNumber": "EXISTING_VALUE"
  }'

Expected Response (409):

{
  "success": false,
  "error": "Duplicate record found",
  "duplicates": [
    {
      "field": "school_recognition_no",
      "value": "EXISTING_VALUE",
      "message": "School recognition number already exists"
    }
  ]
}

Test 2: Update School to Duplicate Value

curl -X PUT http://localhost:3000/api/schools/123 \
  -H "Content-Type: application/json" \
  -d '{
    "schoolRecognitionNumber": "EXISTING_VALUE"
  }'

Expected Response (409): Same as above

Test 3: Create School with Unique Values

curl -X POST http://localhost:3000/api/schools \
  -H "Content-Type: application/json" \
  -d '{
    "name": "New School",
    "district": "Test",
    "schoolRecognitionNumber": "UNIQUE_VALUE_123"
  }'

Expected Response (201): School created successfully

---

Behavior

✅ Allowed

• Creating schools with at least one unique identifier field
• Creating schools with one or more identifier fields (not all three required)
• Creating schools where different schools use different identifier fields
  • Example: School A uses school_recognition_no, School B uses general_register_no
• Updating a school without changing identifier fields
• Updating a school to unique identifier values
• Multiple NULL values (allowed - only non-NULL values must be unique)

❌ Prevented

• Creating a school without any identifier (no school_recognition_no, general_register_no, or affiliation_no)
• Creating a school with a school_recognition_no that already exists
• Creating a school with a general_register_no that already exists
• Creating a school with an affiliation_no that already exists
• Updating a school to remove all identifier fields
• Updating a school to use any duplicate identifier value

---

Model Methods Added

School.checkForDuplicates(schoolData, excludeId)

Checks for duplicate values before insert/update.

Parameters:

• schoolData - Object containing school data
• excludeId - Optional ID to exclude from check (for updates)

Returns: Array of duplicate errors (empty if no duplicates)

Example:

const errors = await School.checkForDuplicates({
  school_recognition_no: '4260/37'
});

if (errors.length > 0) {
  console.log('Duplicates found:', errors);
}

School.findByGeneralRegisterNo(generalRegisterNo)

Find school by general register number.

School.findByAffiliationNo(affiliationNo)

Find school by affiliation number.

---

Files Modified

1. database/migrations/007_add_unique_constraints_schools.sql

   • SQL migration to add unique indexes

2. scripts/migrate-unique-constraints.js

   • Migration script with duplicate checking

3. models/School.js

   • Added checkForDuplicates() method
   • Added findByGeneralRegisterNo() method
   • Added findByAffiliationNo() method
   • Updated create() with duplicate checking
   • Updated update() with duplicate checking

4. routes/schools.js

   • Added duplicate error handling (409 status)
   • Returns detailed error messages

5. package.json

   • Added migrate-unique script

---

Troubleshooting

Error: "duplicate key value violates unique constraint"

This means a duplicate value was inserted despite validation. This can happen if:

• Data was inserted directly via SQL
• Race condition (two requests at the same time)

Solution: Check application logs and ensure proper validation is in place.

Migration fails with existing duplicates

Solution:

1. Review duplicate records
2. Update or delete duplicates
3. Re-run migration

Need to remove unique constraint

-- Remove unique constraints
DROP INDEX IF EXISTS idx_schools_recognition_no_unique;
DROP INDEX IF EXISTS idx_schools_general_register_no_unique;
DROP INDEX IF EXISTS idx_schools_affiliation_no_unique;

---

Summary

✅ Database-level protection - Unique indexes prevent duplicates ✅ Application-level validation - Pre-checks before insert/update ✅ Clear error messages - API returns specific duplicate fields ✅ Migration safety - Checks for existing duplicates before applying constraints

The system now prevents duplicate school records at both the database and application levels!