sql - Add primary key to existing table - Stack Overflow
Join the Stack Overflow Community
Stack Overflow is a community of 7.1 million programmers, just like you, helping each other.
J it only takes a minute:
I have an existing table called Persion. In this table I have 5 columns:
Pdescription
When I created this table, I set PersionId and Pname as the primary key.
I now want to include one more column in the primary key - PMID. How can I write an ALTER statement to do this? (I already have 1000 records in the table)
18.5k83756
drop constraint and recreate it
alter table Persion drop CONSTRAINT &constraint_name&
alter table Persion add primary key (persionId,Pname,PMID)
you can find the constraint name by using the query below:
select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='Persion'
and type_desc LIKE '%CONSTRAINT'
I think something like this should work
-- drop current primary key constraint
ALTER TABLE dbo.persion
DROP CONSTRAINT PK_persionId;
-- add new auto incremented field
ALTER TABLE dbo.persion
ADD pmid BIGINT IDENTITY;
-- create new primary key constraint
ALTER TABLE dbo.persion
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
7,40921534
-- create new primary key constraint
ALTER TABLE dbo.persion
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
is a better solution because you have control over the naming of the primary_key.
It's better than just using
ALTER TABLE Persion ADD PRIMARY KEY(persionId,Pname,PMID)
which yeilds randomized names and can cause problems when scripting out or comparing databases
4,57592641
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain UNIQUE values and column cannot contain NULL Values.
-- DROP current primary key
ALTER TABLE tblPersons DROP CONSTRAINT &constraint_name&
ALTER TABLE tblPersons
DROP CONSTRAINT P_Id;
-- ALTER TABLE tblpersion
ALTER TABLE tblpersion add primary key (P_Id,LastName)
Necromancing.
Just in case anybody has as good a schema to work with as me...
Here is how to do it correctly:
In this example, the table name is dbo.T_SYS_Language_Forms, and the column name is LANG_UID
-- First, chech if the table exists...
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'T_SYS_Language_Forms'
-- Check for NULL values in the primary-key column
IF 0 = (SELECT COUNT(*) FROM T_SYS_Language_Forms WHERE LANG_UID IS NULL)
ALTER TABLE T_SYS_Language_Forms ALTER COLUMN LANG_UID uniqueidentifier NOT NULL
-- No, don't drop, FK references might already exist...
-- Drop PK if exists (it is very possible it does not have the name you think it has...)
-- ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT pk_constraint_name
--DECLARE @pkDropCommand nvarchar(1000)
--SET @pkDropCommand = N'ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT ' + QUOTENAME((SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
--WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
--AND TABLE_SCHEMA = 'dbo'
--AND TABLE_NAME = 'T_SYS_Language_Forms'
----AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms'
---- PRINT @pkDropCommand
--EXECUTE(@pkDropCommand)
-- Instead do
-- EXEC sp_rename 'dbo.T_SYS_Language_Forms.PK_T_SYS_Language_Forms1234565', 'PK_T_SYS_Language_Forms';
-- Check if they keys are unique (it is very possible they might not be)
IF 1 &= (SELECT TOP 1 COUNT(*) AS cnt FROM T_SYS_Language_Forms GROUP BY LANG_UID ORDER BY cnt DESC)
-- If no Primary key for this table
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'T_SYS_Language_Forms'
-- AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms'
ALTER TABLE T_SYS_Language_Forms ADD CONSTRAINT PK_T_SYS_Language_Forms PRIMARY KEY CLUSTERED (LANG_UID ASC)
END -- End uniqueness check
PRINT 'FSCK, this column has duplicate keys, and can thus not be changed to primary key...'
END -- End NULL check
PRINT 'FSCK, need to figure out how to update NULL value(s)...'
32.5k41209318
If you add primary key constraint
ALTER TABLE &TABLE NAME& ADD CONSTRAINT &CONSTRAINT NAME& PRIMARY KEY &COLUMNNAME&
for example:
ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
2,21181933
Try using this code:
ALTER TABLE `table name`
CHANGE COLUMN `column name` `column name` datatype NOT NULL,
ADD PRIMARY KEY (`column name`) ;
6,087103566
ALTER TABLE TABLE_NAME ADD PRIMARY KEY(`persionId`,`Pname`,`PMID`)
23.6k1576131
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
The week's top questions and answers
Important community announcements
Questions that need answers
By subscribing, you agree to the
rev .25821
Stack Overflow works best with JavaScript enabled正在努力加载...
此商品暂不代购哦~
李宁空袭二代高帮篮球鞋耐磨男子CBA专业比赛鞋ABAK035-1-2-5-6-7
品牌类型商家店铺
团购价 ?279
市场价 ?569
折扣 4.9折
681人数参团
?888? 1248
?499? 1299
?749? 1399
商品详情常见问题
特价出售空袭二代篮球鞋，专柜正品，接受虎扑验货，如假白送（不能包邮，望见谅。鞋子加上鞋盒已经超重，鞋盒会用加固纸盒加固，更多一重保障，请放心购买。）李宁CBA空袭II篮球专业比赛鞋，运用简洁的色彩搭配，简单时尚。鞋身大面积贴膜工艺，时尚大气。采用弹性皮料包裹鞋头部位，在运动中提升前掌稳定性。中足TPU稳定托盘，供有效的足弓支撑，在运动过程中下保持双脚的稳定。鞋面上撞色李宁品牌LOGO，细致做工，彰显品牌魅力。鞋底采用橡胶外底，将自然的蜂巢形状与运动中足底受力的运动规律结合，在保持止滑效果的同时达到外观的潮流时尚。
BOUNSE+/反弹科技
Bounse+是一种高反弹耐压缩材料。经测试，Bounse+的垂直回弹性能比传统中底材料好。可以更有效地帮助运动者减少运动时能量损失
DYNAMIC&SHELL/随动装甲
Dynamic&Shell针对运动中容易受伤的脚部关节，将高弹性特殊材料填充到功能模块中，配合表面高弹力网布材料和人体工学设计，对脚部关节起到明显的动态保护与支撑作用，提升运动表现。
&&鞋面：合成革+纺织品
&&鞋底：橡胶+EVA复合底
&&鞋身大面积贴膜工艺，时尚大气
&&采用弹性皮料包裹鞋头部位，在运动中提升前掌稳定性
&&鞋底采用橡胶外底，将自然的蜂巢形状与运动中足底受力的运动规律结合，在保持止滑效果的同时达到外观的潮流时尚
&&BOUNSE+/反弹科技
&&DYNAMIC&SHELL/随动装甲
1、什么是识货团购?
用户下单识货团购是以为用户和卖家提供更直接、便捷的交易渠道为初衷，以为双方提供良好、诚信、互惠互利的网上购物
体验为目标的专业运动类正品导购平台。通过对卖家资质、产品质量、价格优势三个维度严格的要求和筛选，为用户提供更
专业、直接的导购服务，满足用户足不出户就可以在网上放心购物的需求。
2、识货团购购买流程是怎样的？
在识货团购页面挑选自己喜欢的商品进入对应淘宝店家，拍下相应商品后，向店家旺旺客服报上团购暗号（shihuoXXXXXX
或者识货团购），待客服改价后完成付款。
如果用户通过上述流程得到的最终价格与团购页面标注价格不符，欢迎反应给识货团购，识货团购将从用户利益出发核实情
况并给予用户最终答复。
3、识货团购的商品是否保证是正品？在识货团购买了商品后发现商品有瑕疵，或者不喜欢，想申请退换货，该如何操作？
识货团购的商品都是经过专人经过严谨的审核流程允许上线；每一家参与团购的卖家，识货都经由专人进行过资质审核；
识货团购也欢迎且建议用户在收到球鞋后前往虎扑体育装备论坛进行鉴定来维护自己应有的权益。
如若出现残、次、假等问题，欢迎用户尽快向淘宝和识货官方进行反馈，识货君将第一时间站在维护用户利益的角度交涉并解决问题，尽力给双方一个满意的答复。
4、我在识货团购看中一款商品，但是进入淘宝店铺后发现商品已经没有我的码数或者提示已经下线，我该怎么办？
对于一些识货团购中的热门商品，由于用户的热情很高或商品数量有限，可能会出现团购商品下线前商品断码或者售罄的情
况。在此建议用户多关注团购页面中“预览”一栏，当明确自己喜欢的商品的确切上线时间后，待商品上线后第一时间前往
淘宝店家购买，这样会增加商品的购得几率。
如若发现团购商品实际已经售罄，或者产品内容与团购内容不符，欢迎用户及时向团购官方反馈。
5、我在识货团购上并没有发现自己喜欢的商品。
识货团购正在尽全力为用户提供价格实惠，品质优秀，种类多样的正品商品，不过识货团购也将努力在未来做到更好，满足
更多用户的需求。希望用户朋友们可以支持我们！
虎扑（上海）文化传播股份有限公司 沪B2-NBA季后赛伊始，16支球队整装待发，不一样的阵容不一样的精彩！极品赛事，尽在哇哈！
PS:论坛整风行动开展以来，各类伸手党纷纷落马，新赛季开始哇哈仍不会松懈，只会加大打击力度，违规回帖行为封禁期限一个赛季起，情节严重者直接永久，绝不姑息！
规范自身回帖行为请参见最新公告：/thread--1.html
1 / 757 页
- [阅读权限 10]
- [阅读权限 10]
- [阅读权限 5]
- [售价 6 哇币]
- [售价 6 哇币]
- [阅读权限 10]
- [阅读权限 10]
- [阅读权限 10]
- [阅读权限 10]
- [售价 10 哇币]
- [售价 6 哇币]
- [阅读权限 10]
- [阅读权限 5]
- [阅读权限 10]
- [阅读权限 5]
- [阅读权限 10]
- [阅读权限 5]
- [阅读权限 5]
- [售价 8 哇币]
- [阅读权限 5]
- [售价 12 哇币]
- [售价 7 哇币]
- [售价 8 哇币]
- [售价 8 哇币]
- [售价 7 哇币]
- [售价 10 哇币]
- [阅读权限 10]
- [售价 10 哇币]
- [售价 6 哇币]
- [阅读权限 10]
- [阅读权限 10]
- [阅读权限 10]
- [售价 4 哇币]
1 / 757 页
Powered by