I'm developing a Node Backend(CRUD Application) which is connected with Microsoft SQL Server Database. But the table that I have to use does not have any unique key column. I'm not allowed to add a unique key column to the table as well. In this case how can I specifically select a row to do Update & Delete operations?
Note : Create data, Read/View all data is possible to do without a unique key but how to do other operations such as read/view a specific row, update a specific row, delete a specific row?
Columns that I have in the table with data types :
Cus_no - varchar(20),
RangeName - char(8),
Color - char(2000),
BladeTExt - char(2000),
SpecialNotes - char(3000),
box_qty - int
Here is a picture of the table with some data
What I have tried:
I have created some event sql script files in my Node application,
createEvent.sql - To insert data to the table
INSERT INTO [dbo].[PBS_BandWrapsMaster]
(
[Cus_no],
[RangeName],
[Color],
[BladeTExt],
[SpecialNotes],
[box_qty]
)
VALUES
(
@customer_no,
@range,
@color,
@blade_text,
@special_notes,
@box_qantity
)
SELECT SCOPE_IDENTITY() AS customer_no
eventslist.sql - To view/read all data
SELECT [Cus_no],
[RangeName],
[Color],
[BladeTExt],
[SpecialNotes],
[box_qty]
FROM [dbo].[PBS_BandWrapsMaster]
eventbyId.sql - To view/read specific row of data
SELECT [Cus_no],
[RangeName],
[Color],
[BladeTExt],
[SpecialNotes],
[box_qty]
FROM [dbo].[PBS_BandWrapsMaster]
WHERE [eventId]=@eventId 'Here what should I put instead of event ID to filter and get a specific row since there is no ID field or any unique column in the table'
updateEvent.sql - To update a specific row fields
UPDATE [dbo].[PBS_BandWrapsMaster]
SET [Cus_no]=@customer_no,
[RangeName]=@range,
[Color]=@color,
[BladeTExt]=@blade_text,
[SpecialNotes]=@special_notes,
[box_qty]=@box_qantity
WHERE [eventId]=@eventId 'What should I use instead eventId?'
SELECT [Cus_no],
[RangeName],
[Color],
[BladeTExt],
[SpecialNotes],
[box_qty]
FROM [dbo].[PBS_BandWrapsMaster]
WHERE [eventId]=@eventId 'What should I use instead eventId?'
deleteEvent.sql - To delete a specific row
DELETE [dbo].[PBS_BandWrapsMaster]
WHERE [eventId]=@eventId
'Was thinking to replace [eventId]=@eventId with [Cus_no]=@customer_no AND [RangeName]=@range'
index.js - Node file which I try to send and receive requests
In this file with what should I replace all the eventId's?
'use strict';
const utils = require('../utils');
const config = require('../../config');
const sql = require('mssql');
const getEvents = async () => {
try {
let pool = await sql.connect(config.sql);
const sqlQueries = await utils.loadSqlQueries('events');
const eventsList = await pool.request().query(sqlQueries.eventslist);
return eventsList.recordset;
} catch (error) {
console.log(error.message);
}
}
const getById = async(eventId) => {
try {
let pool = await sql.connect(config.sql);
const sqlQueries = await utils.loadSqlQueries('events');
const event = await pool.request()
.input('eventId', sql.Int, eventId)
.query(sqlQueries.eventbyId);
return event.recordset;
} catch (error) {
return error.message;
}
}
const creatEvent = async (eventdata) => {
try {
let pool = await sql.connect(config.sql);
const sqlQueries = await utils.loadSqlQueries('events');
const insertEvent = await pool.request()
.input('Cus_no', sql.VarChar(20), eventdata.Cus_no)
.input('RangeName', sql.Char(8), eventdata.RangeName)
.input('Color', sql.Char(2000), eventdata.Color)
.input('BladeTExt', sql.Char(2000), eventdata.BladeTExt)
.input('SpecialNotes', sql.Char(3000), eventdata.SpecialNotes)
.input('box_qty', sql.Int, eventdata.box_qty)
.query(sqlQueries.createEvent);
return insertEvent.recordset;
} catch (error) {
return error.message;
}
}
const updateEvent = async (eventId, data) => {
try {
let pool = await sql.connect(config.sql);
const sqlQueries = await utils.loadSqlQueries('events');
const update = await pool.request()
.input('eventId', sql.Int, eventId)
.input('Cus_no', sql.VarChar(20), data.Cus_no)
.input('RangeName', sql.Char(8), data.RangeName)
.input('Color', sql.Char(2000), data.Color)
.input('BladeTExt', sql.Char(2000), data.BladeTExt)
.input('SpecialNotes', sql.Char(3000), data.SpecialNotes)
.input('box_qty', sql.Int, data.box_qty)
.query(sqlQueries.updateEvent);
return update.recordset;
} catch (error) {
return error.message;
}
}
const deleteEvent = async (eventId) => {
try {
let pool = await sql.connect(config.sql);
const sqlQueries = await utils.loadSqlQueries('events');
const deleteEvent = await pool.request()
.input('eventId', sql.Int, eventId)
.query(sqlQueries.deleteEvent);
return deleteEvent.recordset;
} catch (error) {
return error.message;
}
}
module.exports = {
getEvents,
getById,
creatEvent,
updateEvent,
deleteEvent
}
eventController.js - Need to find a way to replace all eventId's in this file too
'use strict';
const eventData = require('../data/events');
const getAllEvents = async (req, res, next) => {
try {
const eventlist = await eventData.getEvents();
res.send(eventlist);
} catch (error) {
res.status(400).send(error.message);
}
}
const getEvent = async (req, res, next) => {
try {
const eventId = req.params.id;
const event = await eventData.getById(eventId);
res.send(event);
} catch (error) {
res.status(400).send(error.message);
}
}
const addEvent = async (req, res, next) => {
try {
const data = req.body;
const insert = await eventData.creatEvent(data);
res.send(insert);
} catch (error) {
res.status(400).send(error.message);
}
}
const updatEvent = async (req, res, next) => {
try {
const eventId = req.params.id;
const data = req.body;
const updated = await eventData.updateEvent(eventId, data);
res.send(updated);
} catch (error) {
res.status(400).send(error.message);
}
}
const deleteEvent = async (req, res, next) => {
try {
const eventId = req.params.id;
const deletedEvent = await eventData.deleteEvent(eventId);
res.send(deletedEvent);
} catch (error) {
res.status(400).send(error.message);
}
}
module.exports = {
getAllEvents,
getEvent,
addEvent,
updatEvent,
deleteEvent
}
I do really appreciate your time and consideration all! Please feel free to show my errors if there's any other than the eventId issue.