1

I want my table to have unique rows, but not unique columns. Is that an option? I want to be able to have same values on multiple rows in the same column, but not a 100% copy of another row...

I saw this question but it is about MySQL, and I need it for SQL SERVER.

My specific table (conversation-staffer) is this:

conversation_id | staffer_id | handled

Thanks.

Community
  • 1
  • 1
Michael Haddad
  • 4,085
  • 7
  • 42
  • 82

1 Answers1

4

You could add a unique index that includes all the columns of the table:

CREATE UNIQUE INDEX <index name> ON <tablename>(<columns>);

Alternatively, you could set all columns as your Primary Key. But you have to drop the PK first if the table already has.

ALTER TABLE <tablename> DROP CONSTRAINT <constraint_name>;    
ALTER TABLE <tablename> ADD PRIMARY KEY (<columns>);
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • That way I could have to different row with some of the columns similar? – Michael Haddad May 23 '16 at 07:39
  • If you put all the columns in the unique index or the PK, you can't have rows with same data on all columns. If some of the columns can have similar data, then you exclude them in the column list on your index. – Felix Pamittan May 23 '16 at 07:40
  • 5
    If you add a `UNIQUE INDEX` containing *all* the columns then each unique *combination* of row values can only be stored once, but inside each individual column, or subsets of columns, you can have duplicates. – Lasse V. Karlsen May 23 '16 at 07:41
  • Could you please edit your code for the table "conversation-staffer" and the columns "conversation_id", "staffer_id", "handled"? I am not very strong in SQL :\ – Michael Haddad May 23 '16 at 07:44
  • And please upvote the question. I do not understand why it was downvoted. If it is worth answering then it is worth questioning. – Michael Haddad May 23 '16 at 07:44
  • Thanks, but I am not sure how to insert them... I tried this: `CREATE UNIQUE INDEX IndexName ON [conversation-staffer](,,); ` – Michael Haddad May 23 '16 at 07:45
  • Ho! I have succeeded. Thanks A LOT! – Michael Haddad May 23 '16 at 08:02