primary key

• tripri

  SSCommitted

  Points: 1584

  More actions

  July 8, 2010 at 11:13 am

  #221063

  Hi

  i got a table with three columns.

  col1 col2 col3

  now i want to add a primary key constraint to "col1" in that existing table.

  Col1 got repeated values. But when all the columns are considered there are no repeated or duplicate values.

  for example:

  col1 col2 col3

  fun 1 2

  fun 3 4

  fun 2 3

  name 1 3

  game 2 3

  game 4 4

  now here in the above table i want to create a primary key on col1. Is it possible to create a primary key on that. Or else is there any way to create another key on that col1 as i need to use that col1 to create a foreign key on column of another table which refernces this primary key in col1

• Dugi

  SSCoach

  Points: 17998

  More actions

  July 8, 2010 at 11:17 am

  #1190389

  I don't know the correct database structure, but you can create composite key that includes three columns as Primary Key.

  ============================================================
  SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
  http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

• jeff.mason

  SSCrazy Eights

  Points: 9585

  More actions

  July 8, 2010 at 11:29 am

  #1190396

  Because of the non-uniqueness of col1 by itself, it alone cannot be a primary key by definition and in all truth it is not a generic key candidate at large. While it is true to say that a composite primary key can be made of the three columns, if you wish for this to link to a foreign key, then this would not be best served as a composite primary key. Better to make a unique index/constraint on the three columns to ensure the uniqueness of all three columns in combination, and then add an identity int primary key that has random values and let THAT be the reference to the foreign key. It's a smaller footprint and will perform better in joins.

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  July 8, 2010 at 11:50 am

  #1190410

  tripri, search BOL(Books Online) for 'ALTER TABLE' for syntax, and you could probably find on google endless descriptions of what Jeff was talking about. Basically, here is how you can create a unique constraint after the fact.

  IF OBJECT_ID('TempDB..#DropThis','u') IS NOT NULL

  DROP TABLE #DropThis

  GO

  CREATE TABLE #DropThis

  (

  Col1 VARCHAR(20),

  Col2 INT,

  Col3 INT

  )

  GO

  ALTER TABLE #DropThis

  ADD CONSTRAINT CS_#DropThis_Col1_Col2_Col3_UNIQUE UNIQUE (col1,col2,col3)

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.