modify table

[iccsi]

I tried to modify a field from non-key field to a primary key in the
table, but SQL Server 2000 does not let me to do it.

I thought that I can modify the tables to add, modify the fields as
needed. It looks like it is not.
I would like to know any situation that I can not modify or add fields
in the table.

Your information and help is great appreciated,


iccsi

[Bob Barrows]

Post by iccsi
I tried to modify a field from non-key field to a primary key in the
table, but SQL Server 2000 does not let me to do it.
I thought that I can modify the tables to add, modify the fields as
needed. It looks like it is not.
I would like to know any situation that I can not modify or add fields
in the table.
Your information and help is great appreciated,
iccsi

Symptoms, please?
You can certainly drop/add constraints, regardless of the version of SQL
Server.. Tell us what you did and what happened when you did it. Are you
sure the field is qualified to be the primary key? No duplicate data in the
field? An easy way to find out is to group by it:

select keycandidate from table
group by keycandidate
having count(*) > 1

If that returns results, then the field is not qualified to be a primary key
on its own, it needs to be combined with other fields so that grouping by
them results in unique identification of each row.

[Erland Sommarskog]

Post by iccsi
I tried to modify a field from non-key field to a primary key in the
table, but SQL Server 2000 does not let me to do it.
I thought that I can modify the tables to add, modify the fields as
needed. It looks like it is not.
I would like to know any situation that I can not modify or add fields
in the table.
Your information and help is great appreciated,

To make a column a primary key, three conditions must be fulfilled:

1) The column must not be nullable.
2) There must not already be a primary key defined on the table.
3) The values in the column must be unique.

If these conditions are fulfilled, you can do:

ALTER TABLE tbl ADD CONSTRAINT pk_tbl PRIMARY KEY(col)

If the column already has a primary key, you need to drop that key first:

ALTER TABLE tbl DROP CONSTRAINT <name>

You can find the name with sp_helpconstraint.

If you per chance are talking about the Table Designer, all I can say
is stay away! The Table Designer is very buggy when it comes to modifying
existing tables. And this applies to both SQL 2000 and SQL 2008. The
looks are different, but the bug has been carefully maintained and are
the same in both versions!

[iccsi]

Post by Erland Sommarskog

Post by iccsi
I tried to modify a field from non-key field to a primary key in the
table, but SQL Server 2000 does not let me to do it.
I thought that I can modify the tables to add, modify the fields as
needed. It looks like it is not.
I would like to know any situation that I can not modify or add fields
in the table.
Your information and help is great appreciated,

1) The column must not be nullable.
2) There must not already be a primary key defined on the table.
3) The values in the column must be unique.
  ALTER TABLE tbl ADD CONSTRAINT pk_tbl PRIMARY KEY(col)
  ALTER TABLE tbl DROP CONSTRAINT <name>
You can find the name with sp_helpconstraint.
If you per chance are talking about the Table Designer, all I can say
is stay away! The Table Designer is very buggy when it comes to modifying
existing tables. And this applies to both SQL 2000 and SQL 2008. The
looks are different, but the bug has been carefully maintained and are
the same in both versions!
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thanks for helping,
I change "Prevent saving changes that require table re-creation"
option from true to false then it works.
Thanks again for helping,

iccsi

[Erland Sommarskog]

Post by iccsi
I change "Prevent saving changes that require table re-creation"
option from true to false then it works.

No it doesn't. That checkbox is there for a reason. To stop you from
entering a very buggy territory. You need to learn to use ALTER TABLE
to make your table changes. And for that matter, so that the Table Designer.
The reason you get that message is because the Table Designer thinks
it must drop and recreate the table, which is a dangerous operation
if you don't do it right. And the Table Designer doesn't.

[iccsi]

Post by Erland Sommarskog

Post by iccsi
I change "Prevent saving changes that require table re-creation"
option from true to false then it works.

No it doesn't. That checkbox is there for a reason. To stop you from
entering a very buggy territory. You need to learn to use ALTER TABLE
to make your table changes. And for that matter, so that the Table Designer.
The reason you get that message is because the Table Designer thinks
it must drop and recreate the table, which is a dangerous operation
if you don't do it right. And the Table Designer doesn't.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thanks a million for the message,

iccsi