Uniqueness on multiple columns - update query

Question

I have made unique keys for four columns in sql server - I want that I want to check this uniqueness in asp.net c# (front-end)... I want to know which approach is better - back-end keys or front-end checking ?? Because, the record gets updated .. Eg - I select a record from Gridview , and make the EmployeeID, SubjectID, same - still - instead of giving error, it gets updated. How to stop the updation at this point ? Please guide and help ! Also, I received a reply - Hi , if you have to check the uniqueness of more then a column so you should have to check from back end with stored procedure, Proc make it fast to check Please guide further... Please help

Shubham Kumar · Answer

You can set the composite key on your transaction table like primary key here is the step: Open the design table tab Highlight your two or more INT fields (Ctrl/Shift+click on the grey blocks in the very first column) Right click -> Set primary key or from query like this ALTER TABLE myTable ADD PRIMARY KEY (Column1,Column2) GO

Shubham Kumar · Answer

Hi, Riddhi as per your question i am trying same thing on my machine its working well no problem has occurred. Do one set the Flag by default to 1 when u delete make its zero and again insert new.

Riddhi Valecha · Answer

Hi Shubham.... I have one more query... In my table, there are total 10 fields / Columns. And here, I have to put a unique constraint on total 5 columns. Also, last column (10th Column) is - Flag - bit (datatype). This is kept for active / inactive records. When user inserts any record, then the value of Flag = 1 / true. When user deletes any record, an update query is fired - Update tblSubjectDetails set Flag = 0 where ID = 80 (ID is primary / auto increment column). While Loading - only active records are displayed - Select * from tblSubjectDetails where Flag = 1 Now, If I put uniqueness for 5 fields/columns , by right clicking on columns. After that I delete a record, then flag =0 Again if I put the record with same values - violating the uniqueness, then the record does not insert and the user complaints- the record is not shown and system does not allow to insert. Please guide for the same. Thanks in advance.

Shubham Kumar · Answer

hi riddhi!! its possible but you have to write nested condition in stored procedure. But for this type of situation sql introduce the composite key concept. The way you want to write the logic its all depend on you..

Riddhi Valecha · Answer

Hi... This solve my issue for the time being... But still, is there any logic that we can write in stored procedure for uniqueness of columns , instead of making it on columns ?

Riddhi Valecha · Answer

How to use composite key ? I am using SQL Server as back-end ! Is there a way to check it in stored procedure ? Or from front-end ?

Shubham Kumar · Answer

Hi Riddhi, try to use composite key (multiple column primary key ) instead of unique constrain i think that will be use full and its prevent update of record if row is not unique

Uniqueness on multiple columns - update query

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (7)