0

I have a table with the following fields:

  searchID ( I have set this as a primary key )
  SearchText nvarchar(MAX)
  .......
  and so on

I want to make SearchText also as an additional primary field. How should this be done? Is that a good procedure to make two primary columns for a table?

Venkat
  • 1,702
  • 2
  • 27
  • 47
  • 4
    Not really a problem to have 2 columns as a primary key, but not a good choice that one of them is a `nvarchar(MAX)` – Lamak Oct 15 '15 at 13:40
  • You can create a composite primary key. Refer to http://stackoverflow.com/questions/3922337/how-to-create-composite-primary-key-in-sql-server-2008 – user3541575 Oct 15 '15 at 13:42
  • To be clear you can't have 2 primary keys, but you can have a composite primary key which is a primary key consisting of more than 1 column. – Sean Lange Oct 15 '15 at 13:42
  • 1
    This is obviously an XY problem. Why do you want `SearchText` to be a primary key? – Luaan Oct 15 '15 at 13:44

2 Answers2

1

It's impossible to have more than one primary key on one table and store unique values.

Your primary key must be as short as possible.

If you need to keep unique data in other column, you can create unique key on this column:

CREATE TABLE dbo.Table
(
   SearchText nvarchar(MAX)NOT NULL, 
   CONSTRAINT AK_SearchText UNIQUE(SearchText) 
);

Or with management studio:

enter image description here

Backs
  • 24,430
  • 5
  • 58
  • 85
  • @Venkat select your database -> your table -> `Indexes` -> right click -> `New Index` -> `Non-Clustered index` -> add colums and check `Unique` – Backs Oct 15 '15 at 13:48
0

You can create composite key as below

create table myTable 
(
    SearchId int not null,
SearchText nvharchar not null

) GO

-- Add Constraint
ALTER TABLE myTable
ADD CONSTRAINT pk_myConstraint PRIMARY KEY (SearchId ,SearchText)
GO
DevelopmentIsMyPassion
  • 3,541
  • 4
  • 34
  • 60