Assign A Primary Key to a Table

Question

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->

I am having a heck of a time assigning a PK to a Table.  In Access, I have the PK set, and I import into SQL Server.  When the data is imported, the PK is gone (looking in Design view).  So I go into Design view.  The PK is called ID.  The data type for the ID Field is int, and the Allow Nulls is unchecked.  I right click to assign a PK, then close the window and I get a message the reads ‘Saving changes is not permitted’.  How can I assign a PH to this Field?  I even tried deleting all data with ‘delete * from Capital. dbo. USA ’ and I get a message that reads ‘Msg 102 Level 15, State 1, Line 1, Incorrect Syntax near ‘*’’.  I’m pretty sure that’s how to delete all records from a Table.  I’ve done it before, but can’t do it now.  Is there some kind of protection on my DB?

 

Well, that’s the background.  Basically, my two questions are :

 

How can I assign a Primary Key?

How can I delete all records from a Table?

 

Thanks,

Ryan---

Answer 1

Hi Ryan,

To add a primary key to existing table,

-- But make sure the ID is defined with not null because primary key constraints cannot be defined on a null column

alter table tablename add constraint cnsrt_name primary key (id);

-- To delete all records use this,

delete from dbname.schemaname.tablename

Answer 2

Thanks Lekss, the two things you described are exactly what I tried, but it didn't work.  Is there something else I can do to assign a PK and delete all records from the Table?  I was able to assign a PK to another Table, but all data had to be deleted from the Table before I could assign the PK.  It was kind of easy to delete the data because there weren't too many records; I manually deleted each because SQL Server wouldn't allow me to delete all, as in Delete * From ...

Delete * From [Capital].[dbo].[USA]

Makes sense to me, but this causes an error for some reason.

Any ideas?


Thanks,
Ryan---

Answer 3

Hi Ryan,

I think delete * from is not supported in SQL , but supported in Oracle.

In you case I would typically create a dummy table without any data defining your primary key and then move all your data to new table , finally rename your new table to USA.

---

Thanks, Leks

Answer 4

Hi Ryan,

In SQL Server there are two statements you can use to remove all rows from a table. They are:

DELETE FROM <table>  -- notice that * is not used.  
Look at the Examples section of this Books Online (product documentation)  topic: http://msdn.microsoft.com/en-us/library/ms189835(SQL.90).aspx

or

TRUNCATE TABLE  -- described here: http://msdn.microsoft.com/en-us/library/ms177570(SQL.90).aspx


To add a PK constraint, the syntax is this:

ALTER TABLE <table_name> 
ADD CONSTRAINT <constraint_name> PRIMARY KEY (column_name)

For more information about creating PK constraints, see these Books Online topics:
http://msdn.microsoft.com/en-us/library/ms190273(SQL.90).aspx  -- ALTER TABLE
http://msdn.microsoft.com/en-us/library/ms181043(SQL.90).aspx  -- Creating and Modifying Primary Key Constraints

If you continue to encounter errors, please report the exact syntax you are using and the exact error message you receive.  Doing so makes assisting you much easier and quicker.

Kind Regards,

Gail

---

Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 5

Thanks Gain!  I deleted the data in my Table and reimported everything.  That part looks good.  However, I am still not able to get the PR on the Table.  I looked at your example and the first and third parts make sense, but I am not sure how to add a Constraint.  I looked at a few examples online, including the 2 you gave me, but couldn't get it working for my example.
 
How come i can't go into Design view, right-click on the Field, and assign a PK there?  In Access it works just fine.  In SQL Server, it never works.  Why is this an option if a user can't use it.

Thanks,
Ryan--

Answer 6

Hi Ryan,

You can assign Pk in the design view of the TABLE..
 
  Rightclick table -> select design  -> table now opens up in GUI without the data
  From there point to the column that you wanted to set as a PK -- > right click column --> click set primary key or / click check constraints (here you can add one)

---

Thanks, Leks

Answer 7

Thanks Lekss!!  That's exactly what I did.  When I make the change and then close the window, I get an error message that reads 'saving chages is not permitted', bah, blah, blah.  There must be something in SQL server that auto-protects the Table, and prevents changes, but why would you want that?  Even if I completely delete every record in the Table, so the Table has absolutely nothing in it, you STILL can not assign a PK.  I just get that same message over and over.  What do people do when they need to make a change to the table?  This is incredibly easy in Access and incredibly difficult in SQL Server.  

Answer 8

Can you get your full error msg ?

---

Thanks, Leks

Answer 9

The tool you are using (SSMS) has a setting to protect you from yourself. If you do a change in the designer which requires for the tool to re-create the table, then it won't allow that unless you configure the tool differently (Tools, Options). I suggest you skip the designer and use TSQL - no more strange surprises!

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 10

You are totally right, Tibork.  I went here:

Tools / Options / Designers/ Table and Database Designers

 

Made the change and everything works fine now.  Why is that feature automatically enabled?  What does it protect you from?

 

Thanks for the help!

Ryan--

Answer 11

The functionality os off by default for a reason. One reason is that it isn't the best thing to re-create a table where an ALTER TABLE would have ben enough (just think of preformance implications). Another is that there are some bugs in that code (seach connect.microsoft.com for details).

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 12

Ah!  Ok.  Well, thanks again for everything!
I've got some books about SQL Server; just have to find time to read them...


Ryan---