alter and primary key

[murzina]

I would like to add a field to the table. Is this the right way to do it?

Alter table Distance Add (zid integer(10000000));

I would like to set one of the fields as a primary key. I did not do it at the creation of the table. Is it possible to set a field as a primary key after the creation of the table?

 

[murzina]

For some reason the million database doesn't fit into integer value. I need some other variable that can hold a million record database. The varchar doesn't have enough space allocated either. What variable I can use?

 

[gmmastros]

To add a column to a table...

Alter Table Distance Add Zid Integer NULL

To create a primary key for a table....

ALTER TABLE Distance ADD CONSTRAINT
PK_Distance PRIMARY KEY CLUSTERED
(
Zid
)

An integer can hold a value more than a million. So can a varchar.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[murzina]

As I enter the code below, I get the error below. It says not enough space allocated. What's wrong? Why does it give the error below? And how do I fix it?



USE Zipcodes
GO

Create Table Distance (zid int(1000000),
ZipCodes varchar(7),
Radius Numeric(5),
KM_MI varchar(30),
Calling_Website varchar(100),
Constraint Distance_zip_pk Primary Key (zid));
GO

Insert into Distance
Values (1, 46835, 5, 'Km', '

http://www.website.com');

GO



Msg 131, Level 15, State 2, Line 2
The size (1000000) given to the column 'zid' exceeds the maximum allowed for any data type (8000).
Msg 208, Level 16, State 1, Line 2
Invalid object name 'Distance'.

 

[gmmastros]

There is only one size for an integer. 4 bytes. That's it. This 4 byte integer value can store numbers in the range of -2,147,483,648 to 2,147,483,647.

Varchar's max out 8000 characters.

Try this:

Create Table Distance (zid int,
                       ZipCodes varchar(7),
                       Radius Numeric(5),
                       KM_MI varchar(30),
                       Calling_Website varchar(100),
Constraint Distance_zip_pk Primary Key (zid));

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom