Uniqueness on multiple columns - update query

Riddhi Valecha

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shubham Kumar10y ago
Accepted 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 Kumar10y ago
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 Valecha10y ago
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 Kumar10y ago
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 Valecha10y ago
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 Valecha10y ago
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 Kumar10y ago
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