0

I am trying to assign an existing column to be the primary key(and another in different table as foreign key) in a table without primary key. I tried this: Altering a column: null to not null, and it didn't work. The column contains number, text and null values.

Could someone create a step-by-step guide and share with everyone here?

This is the error message:

Msg 8152, Level 16, State 13, Line 2
String or binary data would be truncated.

It would great if you can help interpret the error message.

Thanks!

Community
  • 1
  • 1
WHZW
  • 445
  • 6
  • 10
  • Do you already have a column that you want to be your primary key (which has unique values and no nulls) or do you want a new column? By the way, you DON'T want to follow the steps in the link you provided. – Greg the Incredulous Dec 03 '14 at 05:27

2 Answers2

3

To add a primary key to an existing table you can do this:

ALTER TABLE yourTableName ADD yourColumnName INT NOT NULL PRIMARY KEY IDENTITY(1, 1)

which will add a new column and automatically populate it with unique integer values starting from one. To add a foreign key to another table use similar syntax to above but don't make the column NOT NULL until you've worked out how to link it to your existing primary key - which is a whole different question.

ALTER TABLE yourOtherTable ADD yourFKColumnName INT WITH CONSTRAINT [FK_SensibleForeignKeyName] FOREIGN KEY ([yourFKColumnName]) REFERENCES yourTableName([yourColumnName])

I haven't tested it but that should be pretty close to what you need.

Greg the Incredulous
  • 1,676
  • 4
  • 29
  • 42
  • I found 10 null values in the column I want to use as the primary key. Don't think I can drop those. Is creating a new column to be the primary the only option now? Thanks for your answer! – WHZW Dec 03 '14 at 12:55
  • If the column that you want to use has any duplicate values or nulls then you won't be able to use it. Primary key must be non null and unique. – Greg the Incredulous Dec 03 '14 at 20:05
1

Just do a

ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable
PRIMARY KEY CLUSTERED (YourColumnHere)

and you're done. This requires that YourColumnHere is a non-nullable column, of course.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459