Add Primary Key

In order to add a primary key to an existing table we need to use the Alter Table command.

Add a primary key for a new column

[cc lang=”sql”]
ALTER TABLE dbo.Presidents
ADD PresidentID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_PresidentID PRIMARY KEY NONCLUSTERED
[/cc]
Note however that the ordering of the identity field will not be predictable for the records that already exist in the table. For newly inserted records, the identity field will increment properly.

Add a primary key for an existing column

[cc lang=”sql”]
ALTER TABLE dbo.Presidents WITH NOCHECK
ADD CONSTRAINT PK_PresidentNumber PRIMARY KEY NONCLUSTERED (PresidentNumber)
[/cc]

27 comments
Ainoer 24 Dec 2016 at 7:59 am

Hi, can you tell me about add a primary key for an existing table because I don’t understand where as table name and field name where.
help me, please! thank’s for your answer.

Ainoer 24 Dec 2016 at 7:55 am

Hi, can you tell me about your post because I don’t understand where as table name and field name where. thank’s for your answer.
help me, please!

vaishnavi 25 Aug 2014 at 7:05 am

how to create 2 primarykey for one table , i am try this coding but doesn’t working plz help & give suggestion for me.
ALTER TABLE tblAppForm ADD CONSTRAINT pk_Constraints PRIMARY KEY(CanidateID,Category)

Raveen 19 Apr 2016 at 5:56 pm

you cannot have two primary keys for one table. if u want to add two primary keys, the most appropriate attribute should be the primary key while the other should be the alternate key.
hope this is helpful. I am 100% sure with this theory but not sure whether there is another theory when choosing primary keys for tables in sql.

P.K. Sachan 25 Feb 2011 at 12:26 am

Hi Praveen

Please tell me query to insert this string… ’13F6789”28/12/2010 04:15:02 PM’1202556,’I011′,RSE58043,1,’28/12/2010 04:15:02 PM’,Secure_3Ph4W_XXXX,Secure,725559555,6.44,6.45,6.45,0.001,0.00,0.00,0.00,0,0.000,0.000,0.001,1.000,1.000,1.000,1.000,0.0,0.0,0.1,49.69,15,0,15,0,7,0,19,0,98390,1057535,0,

Praveen Yadav 25 Feb 2011 at 5:51 am

Hi Sachan,

I Am able to insert the string you have given.See except single quoute no character will cause problem inserting a string
I will suggest you one technique which may help you to over come this single quote problem.

1.First you add some other special charecters(eg : *,# etc ) in the the place of single qoute,except at the beginning and end.

2.Then execute the below query
Insert into TableName Values(‘ Hi,I*AM FINE ,TIME IS NOW 8:30,”PRAVEEN”,IT*S MINE….!@#^#$%^&() etc… ‘)
Update TableName
Set ColumnName=Replace(ColumnName,’*’,””)

Please note that Your column’s actual value sholud not contain
the special character.Or else use a string and replace it later.In the Above case i am using * Which my actual string not having.

Eg:
Insert into TableName Values(‘ Hi,IsinglequoteAM FINE ,TIME IS NOW 8:30,”PRAVEEN”,ITsinglequoteS MINE….!@#^#$%^&() etc… ‘)
Update TableName
Set ColumnName=Replace(ColumnName,’singlequote’,””)

Hope this problem is ends here.

Best Regards
P.Praveen Yadav

P.K. Sachan 03 Feb 2011 at 5:03 am

Thank u very much P.P.Y. And Derek
One another issue is how can i insert this string in a table by hard code
–> DSAD””FDF’SDF'”SD’AS””FSAS’ MAX DDFDFDFDSDFSDF <–

Praveen Yadav 11 Feb 2011 at 4:01 am

Hi Sachan,

I think the below query will solve your probs

DECLARE @STR VARCHAR(100)
SET @STR=’DSAD””FDF”SDF””SD”AS””FSAS” MAX DDFDFDFDSDF’

Now the @str contains the request string. u can use this variable
in insert like below:
INSERT INTO TABLENAME VALUES(@STR,COL2,COL3,ETC…..)
In General you have to use ” for ‘ in SQL
eg: SELECT ””
Which will give you ‘ as output

Best Regards
Praveen

P.K. Sachan 14 Feb 2011 at 10:58 pm

Hi Praveen

I think some thing mismatch to understand. its creating prob at “Set”..
Actualy this string have none specified single quotes or double quotes, they may be even or odd..example like 1 or 4 or 7 or 8 single quotes and like single quote its may be double quotes. String also may have function like max,min,as,…—->DSAD””FDF min ’SDF’”SD’AS””FSAS’ MAX DDFDFDFDSDFSDF

Praveen Yadav 16 Feb 2011 at 5:49 am

Hi Sachan,

I am not clear abt ur requirement. I think the prob must be with quotes

To insert a string directly into a varchar field,first you should
open two single quotes, like ‘ ‘,then you can add any any any values inside that quotes including double quotes.But if you want to add a single quote you must have to add two single quotes.

Hope that it will help you if not,plz describe your prob , i will try my best to solve it.

Best Regrds
Praveen Yadav

P.K. Sachan 21 Feb 2011 at 6:58 am

Hi Praveen Yadav

you are right.. we can do it by replacing ‘Odd’ Single Quotes into ‘Even’ Single Quotes and placing Double quotes at the beginning and at end of String…Thank you Very Much

Thanks & Regards
Piyush Kr. Sachan

P.K. Sachan 20 Jan 2011 at 12:23 am

Hi Derek

One question is that….we are fire “select” command it picks data according to given condition
and shows us… can i direct save this selected data into any files likes csv,excel by TSQL programming ??

Thank You Very Much

Praveen Yadav 27 Jan 2011 at 2:55 am

Hi P.K.S ,
Yeah we can do..

Before executing the query just press Ctrl+Shift+F.
Then execute the query,it will open a save dialog box,there you can select the supported file format and path where u want to save,then the result will be saved to the file once the query was completed.

Regards
P.Praveen Yadav

P.K. Sachan 27 Dec 2010 at 11:31 pm

Hi Derek

One question is that….we are fire “select” command it picks data according to given condition
and shows us… can i direct save this selected data into any files likes csv,excel by TSQL programming ??

Thank You Very Much

P.K. Sachan 20 Dec 2010 at 10:50 pm

Hi Derek

A very very much thank you………….

P.K. Sachan 18 Dec 2010 at 12:44 am

Hi Derek

i want to know difference between these ” count()” ? , is there any execution difference ?

1. count(*)
2. count(2)

Thank you very much

Derek Dieter 20 Dec 2010 at 2:56 pm

Hi P.K.

There is no difference in SQL 2005 +. From what I remember however, there was a difference in previous versions. (Not too sure if it was SQL 2000 or lower). The idea is that with an ordinal specified, the metadata does not have to be referenced. Now the optimizer is smart enough to know that it does not need to reference the ordinal unless DISTINCT is specified.

Derek

P.K. Sachan 15 Dec 2010 at 11:51 pm

Hi Derek,
Thankyou very much..

please see the code and help me to the questions given below..

create TRIGGER [dbo].[T]
ON [dbo].[Piyush]
FOR DELETE,UPDATE
AS

if Exists(Select * From inserted)
begin
PRINT ‘U’
end
else
begin
PRINT ‘D’
end

1. If i ‘update’ some value in Table ‘Piyush’ than it print ‘U’…Why ? even i have not inserted any new record.

Derek Dieter 20 Dec 2010 at 2:59 pm

Hi P.K.

This happens because the intrinsic table “INSERTED” which is referenced in a trigger, contains all records that were changed. The only problem here is that the intrinsic name of “INSERTED” is probably not the best choice. It should be something like, “changeRecords”. So INSERTED contains both ‘INSERTED’ AND ‘UPDATED’ records.

Derek

P.K. Sachan 05 Dec 2010 at 11:41 pm

Hi Derek,
Thanks a lot..

please see the code and help me to the questions given below..

create TRIGGER [dbo].[T]
ON [dbo].[Piyush]
FOR DELETE,UPDATE
AS

if Exists(Select * From inserted)
begin
PRINT ‘U’
end
else
begin
PRINT ‘D’
end

1. If i ‘update’ some value in Table ‘Piyush’ than it print ‘U’…Why ? even i have not inserted any new record.

P.K. Sachan 29 Nov 2010 at 2:59 am

Hi Derek

Please solve my problem…

1. if i insert 1000 rows by (union all or by other method ‘at a time’) in ‘new’ empty table than it take time like 9 or 10 second and if i insert same 1000 rows than its take time less than 1 second….Why..?

2.after one process if i change some value in column like 10 to 20 or piii to piyy than i insert 1000 rows its take again 9 or 10 sec….Why..?

3. how i insert 1000 rows in 1 second in ‘new’ table OR how i insert updated 1000 rows in 1 second in table..???

Thanks & Regards
P.K. Sachan

Derek Dieter 29 Nov 2010 at 8:39 pm

Hi P.K.,

It’s a little difficult to duplicate this without the code or the environment, but I’ll try my best:

1. Does your table contain any indexes? How big are the rows you are inserting? If you are doing a union to insert data, then it is traveling from your client machine to the server. However if you insert 1000 rows directly from one table in the server to another, then the entire action occurs on the server. It does not go from your desktop to the server. This slowness could be because of network latency, or large rows.

2. Is this the 1000 rows you are inserting from your client machine to the server?

3. The best way is to use SELECT.. INTO. This is the fastest way to insert anything in a new table. If you need to insert 1000 rows from your client machine to the server, and it is slow, then I would suggest trying BCP, OPENQUERY, or SSIS. This will at least help you troubleshoot what the issue is.

Try turning on Client – Statistics in SSMS to see what the latency is: Query -> Include Client Statistics

Compare these statistics to the server-side statistics by using: SET STATISTICS TIME ON -> in the window where you are doing the update. Are the client stats slower? If so, then you have network latency.

Last, look at the execution plan to see where the slowness is occuring: Query -> Include actual execution plan

Good Luck,
Derek

P.K. Sachan 19 Nov 2010 at 11:52 pm

Hi Derek,
Thanks a lot to response me and to clear all my doubts..

please see the code and help me to the questions given below..

create TRIGGER [dbo].[T]
ON [dbo].[Piyush]
FOR DELETE,UPDATE
AS

if Exists(Select * From inserted)
begin
PRINT ‘U’
end
else
begin
PRINT ‘D’
end

1. If i ‘update’ some value in Table ‘Piyush’ than it print ‘U’…Why ? even i have not inserted any new record.

P.K. Sachan 27 Oct 2010 at 12:38 am

Hi Derek

i have one more question…please help me

1. if i insert 1000 rows by (union all or by other method ‘at a time’) in ‘new’ empty table than it take time like 9 or 10 second and if i insert same 1000 rows than its take time less than 1 second….Why..?

2.after one process if i change some value in column like 10 to 20 or piii to piyy than i insert 1000 rows its take again 9 or 10 sec….Why..?

3. how i insert 1000 rows in 1 second in ‘new’ table OR how i insert updated 1000 rows in 1 second in table..???

Thanks & Regards
P.K. Sachan

derek dieter 30 Oct 2010 at 7:26 am

Hi again P.K.

Without seeing the code it’s difficult to understand what’s happening. Can you send it to derek @ sqlserverplanet. Com?

Thanks

P.K. Sachan 18 Oct 2010 at 3:51 am

1. in table “T” 4,000 rows coming in 10 sec by ‘job scheduler’
2. i hv put a “code” on AFTER INSERT Trigeer of table ‘T’
3. in “code” i take data from ‘T’ and insert to T1(if data new than insert if duplicate than update with reference primary key)
4.prblem is that…scheduler takes 10 sec than 30 sec than 30 than 1 min…but i want in 10.in whole process.?????
5.any other way for process 1 and 3…????

6. Thank you very much..

Derek Dieter 18 Oct 2010 at 7:26 am

Hi P.K.
If I understand you correctly, you are trying to take the newly created identity field created by the insert and match it to an existing table. You may be able to use this instead of a trigger. You do need SQL 2008 though.

http://sqlserverplanet.com/sql-server-2008/using-the-merge-statements-output-clause-to-match-inserted-identity-fields-with-source-table-fields/

Good luck,
Derek

Featured Articles

 Site Author

  • Thanks for visiting!
css.php