SSMS permits duplicate records in a table, but not subsequent updates

Question

Edit: When I say "SQL Server", I'm really talking about the Management Studio. Sorry if that was confusing.

Oh I hate when things like this happen. I was working with SQL Server yesterday and trying out the PIVOT command to try to figure out how it worked. So I created a new table with four columns, and the first column was going to have the same value for the first few rows.

I added the "value1" to the first row, first column, and hit enter - sine no keys or constraints were added yet, it allowed me to enter down to the next row with NULLs for the other columns on the first row (which is fine). To my surprise, it also allowed me to enter "value1" on the second row and enter down - this should be impossible since now there are two identical rows. However, since I was just messing around, this didn't bother me. So I proceed to create four rows as such:

Table 1

Col1       Col2      Col3     Col4
---------------------------------
Value1     NULL      NULL     NULL
Value1     NULL      NULL     NULL
Value1     NULL      NULL     NULL
Value1     NULL      NULL     NULL

Obviously this is strange and breaks relational theory, but I didn't really care since this is just a table I created to mess around with. However, I just about pulled my hair out at what happened next. After I had this data in, I could not do anything to the table. If I tried to fill in col2, col3, or col4 on any of the rows, SQL Server would scream at me for having duplicate rows: "No row was updated. The data in row 1 was not committed.... The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(4 rows)."

So in other words, SQL Server allowed me to enter in duplicate rows, but when I tried to update the rows to make them unique, it wouldn't allow me, citing that there are duplicate rows as its reason. The worst part is I couldn't even delete any rows either (I get the same error message). The only solution I found once in this scenario was to delete the table and start over - which is ridiculous.

My question is, how can this sort of behavior exist in a well known program that has evolved over a decade? Am I the one being brainless and I should accept SQL Server's behavior? To me this is unacceptable and SQL Server should either never have allowed me to enter duplicate rows in the first place, or it should have allowed me to update the duplicate rows until they were all unique and and then try to save.

This is by no means meant to be some sort of SQL Server hating post. It's relatively rare I run into behavior like this, but when I do, it can really set me behind and drive me crazy. I just don't understand why the program has behavior built in like this. Like why in the world did it let me enter the duplicate rows in the first place if it didn't plan to let me fix it?

I remember working with MS Access back in the day and I would run into the same sort of strange, archaic behavior. A few times I had to copy out huge amounts of data, re-create the table, and copy it back in just because Access had allowed me to do something it shouldn't have, and is now blocking me from any changes to fix it - effectively producing a deadlock.

So what's going on here? Do I need some sort of paradigm change when approaching SQL Server? Is it me or SQL Server that's the problem? (You can say it's me, I can take it.)

Answer 1

All management studio ever does is provides a UI to create some SQL for you and runs it against the DB.

In your case, every time you added a row, it produced an INSERT statement. This is perfectly valid.

When you then tried to use the UI to DELETE or UPDATE a single record out of all these duplicate records it was unable to produce the SQL to do that. The reason being, as there was no key on the table, there is no way of producing a WHERE clause that would represent the record you were trying to UPDATE or DELETE.

It's "error" messages sound perfectly clear and valid to me.

As for your comments:

To my surprise, it also allowed me to enter "value1" on the second row and enter down - this should be impossible since now there are two identical rows. However, since I was just messing around, this didn't bother me.

Obviously this is strange and breaks relational theory, but I didn't really care since this is just a table I created to mess around with.

There is nothing wrong with having a database table that allows duplicates, it is a perfectly valid thing to do if it's what you need. As for not "caring" or being "bothered" that you had allowed duplicates. That is where the mistake lies. It is then that you should've realised you forgot to add a primary key.

Answer 2

A quirk yes, but a flaw, no. It's perfectly legitimate to have a table with no unique key, but it's not possible to delete a row from it with the table editor in SSMS (or Enterprise Manager before it) if there are identical rows.

It isn't SSMS that let you create the duplicate rows, it is you that allowed it when you created your table with no primary key. You can always create an auto-incrementing identity column that would solve the problem.

I wouldn't use the table editor for this sort of stuff, I would script INSERT statements.

Answer 3

I'm not sure how you would expect the tool to know which row to delete if you did not have a unique key. Would you want it to delete just one of the duplicates or both? if just one, what is the key it should use.

The management studio is a tool. It's job is not to enforce perfect table design or basic database 101, which would include having some unique key the majority of the time. What if you did have some crazy business model that required duplicate rows? Wouldn't the complaint then be that the tool prevented duplicate roles?

Bottom line is that 1. You shouldn't be editing or deleting data with the tool anyways. You should be scripting out the statements for most things.
2. You should have a unique key

Can't blame any tool for not having those things in place.

Answer 4

The record editor in Sql Server Management Studio is a small (and relatively unimportant) part of the SQL Server product offering. I think you can safely accept the quirks of the editor without being concerned about the relative quality of the server itself.

Behind the scenes, the Management Studio is executing SQL statements to perform your actions, just as if you typed that SQL yourself. So if you break a rule, you pay the fine.