create primary key on existing table without primary key in SQL Server
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!
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.
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.
上一篇: 从表中获取数据时未设置实体框架对象引用