a set of column to be unique key in sql database

Question

How can I make a set of column to be unique key in sql server database? for example: I have a database that have just one table by columns 1_book 2_page 3_line 4_word i want to search a word in some books and record this information . where is the problem? if it find a words twice or more in a line it will save the same record to table.it is not important for me how many times a word is repeated in a line. i want if a word to be repeated once or more save the information. is there any way to say every record should be unique? searching a record in table before Inserting it to table is not reasonable .isn't it?

Just extend the specifics collected: (book, page, line, *word_index/position*, word) - Consider 1. that "book" itself may need refinement; the same "book" can have many revisions and/or printings; 2. There may be "too much" data collection/refinement going on; 3. book/page/line represent some level of redundancy. — user2864740, Aug 14 '14 at 07:36
redundancy is not matter because my question is just an example. — user3430082, Aug 14 '14 at 07:41

score 0 · Answer 1 · answered Aug 14 '14 at 07:50

You mean, you don't know how to create composite keys? Well:

alter table dbo.Words add constraint PK_Words primary key (
  1_book, 2_page, 3_line, 4_word
);

And if you don't want key violations while adding data, use merge instead of insert (assuming your SQL Server version is 2008 or later).

score 0 · Accepted Answer · edited May 23 '17 at 12:20

0

Just create a unique constraint for your table: (example for ms sql-server)

ALTER TABLE <yourtablename> ADD CONSTRAINT
            UniqueEntries UNIQUE NONCLUSTERED
    (
            1_book, 2_page, 3_line, 4_word
    )

If you do not want to get errors and simply ignore additional adds of the same word in the same line, you can extend the constraint with IGNORE_DUP_KEY = ON

Example:

ALTER TABLE <yourtablename> ADD CONSTRAINT
            UniqueEntries UNIQUE NONCLUSTERED 
    (
                1_book, 2_page, 3_line, 4_word
    ) WITH (IGNORE_DUP_KEY = ON)

Inserts with already existing records will then just be silently ignored.

edited May 23 '17 at 12:20

Community

1
1

answered Aug 14 '14 at 07:53

Marwie

3,177
3
28
49

thank you. this helped me : " I also found you can do this via, the database diagrams. By right clicking the table and selecting Indexes/Keys... Click the 'Add' button, and change the columns to the column(s) you wish make unique. Change Is Unique to Yes. Click close and save the diagram, and it will add it to the table. " – user3430082 Aug 14 '14 at 08:25

a set of column to be unique key in sql database

2 Answers2