Altering Primary Key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Altering Primary Key

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-05-13 : 05:39:01

Hi All,

The msdn documentatio says "However, you cannot change the length of a column defined with a PRIMARY KEY constraint"

but i am able to increase the size of the column even Primary column is there on the column.

please clarify this?

Link:
<<http://msdn.microsoft.com/en-us/library/ms181043(v=sql.105).aspx>>

M.MURALI kRISHNA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-13 : 06:45:24

can you illustrate how you're doing this?
Are you sure you're not dropping PK prior to doing the change?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-13 : 08:36:16

quote:
Originally posted by visakh16

can you illustrate how you're doing this?
Are you sure you're not dropping PK prior to doing the change?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

The documentation is not exactly precise, but not totally wrong either. In the following, the first alter statement succeeds because it will not materially affect data. The second statement will fail probably because it can potentially affect data (even if currently there is no data in the table)

CREATE TABLE tmp(id VARCHAR(32) NOT NULL PRIMARY KEY CLUSTERED);

-- SUCCEEDS
ALTER TABLE tmp ALTER COLUMN id VARCHAR(64) NOT NULL;

-- FAILS
ALTER TABLE tmp ALTER COLUMN id VARCHAR (32) NOT NULL;

DROP TABLE tmp;

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-13 : 12:59:14

I ran this script and I get a failure after every attempt to alter the existing primary key column:[CODE]create table dbo.Junk (
ti tinyint not null identity(1, 1),
vc varchar(10) not null,
c char(10) not null
)
go
--------------------------------------------------
alter table dbo.Junk
add constraint PK_Junk primary key (ti);
go
alter table dbo.junk -- Failure
alter column ti int;
go
alter table dbo.Junk
drop constraint PK_Junk;
go

--------------------------------------------------
alter table dbo.Junk
add constraint PK_Junk primary key (vc);
go
alter table dbo.junk -- Failure
alter column vc varchar(20);
go
alter table dbo.junk -- Failure
alter column vc varchar(5);
go
alter table dbo.Junk
drop constraint PK_Junk;
go

--------------------------------------------------
alter table dbo.Junk
add constraint PK_Junk primary key (c);
go
alter table dbo.junk -- Failure
alter column c char(20);
go
alter table dbo.junk -- Failure
alter column c char(5);
go
alter table dbo.junk -- Failure
alter column c varchar(10);
go
alter table dbo.Junk
drop constraint PK_Junk;
go
drop table dbo.Junk[/CODE]

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-13 : 14:45:32

Bustaz, in your second section where you are changing the column vc, include NOT NULL in the column specification and it should succeed.

alter table dbo.junk	-- Failure should succeed
alter column vc varchar(20) NOT NULL;
go

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-13 : 19:06:23

Expanding the varchar PK does succeed when the not null is specified. Thanks for the feedback. Interestingly, the same does not hold true for the CHAR PK. I assume this is due to the absolute need to rebuild the index.

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-05-15 : 06:49:09

If we define a primary key on a column then it holds NOT NULL and Unique automatically.
Then what is the significanse of writing NOT NULL again while defining primary key?

And why the altering column size is closely associate with this NOT NULL constraint?

Thanks

M.MURALI kRISHNA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 07:08:00

quote:
Originally posted by mmkrishna1919

If we define a primary key on a column then it holds NOT NULL and Unique automatically.
Then what is the significanse of writing NOT NULL again while defining primary key?

And why the altering column size is closely associate with this NOT NULL constraint?

Thanks

M.MURALI kRISHNA

NOT NULL is to ensure column doesnt accept NULLs
unless you make a column NOT NULL you cant create primary key on it
It has to be specified as default option is allow NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-05-15 : 08:36:47

Visakh,

CREATE TABLE tmp(id VARCHAR(32));
i executed this statement in my DB it created a tmp table with id column as nullable,means by default column will allow null values

I am able to create a primary key on a column(id) without specifing NOT NULL externally for that column from below query.
CREATE TABLE tmp(id VARCHAR(32) constraint pk_temp_id PRIMARY KEY);

Thanks

M.MURALI kRISHNA

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-15 : 09:39:51

quote:
Originally posted by mmkrishna1919

I am able to create a primary key on a column(id) without specifing NOT NULL externally for that column from below query.

When you create Primary Key on a field, the field gets atomatically defined as NOT NULL. In other words you cannot create primary key on a "NULLABLE" column

CREATE TABLE tmp (id VARCHAR(32) NOT NULL); -- will work
drop table tmp
CREATE TABLE tmp (id VARCHAR(32) NOT NULL constraint pk_temp_id PRIMARY KEY); --will work
drop table tmp
CREATE TABLE tmp (id VARCHAR(32) NULL constraint pk_temp_id PRIMARY KEY); -- wont' work
drop table tmp
CREATE TABLE tmp (id VARCHAR(32) NULL ); -- will work
ALTER Table tmp add constraint PK_tmp Primary Key (id); -- won't work having similar error as that of the above

Cheers
MIK

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-05-15 : 10:00:42

Thanks Mik_2008 for clarifying NOT NULL issue.

And one more clarification is as "Bustaz Kool" said

Why altering(increasing size) of varchar column with NOT NULL is successfull where as for Char data type column it's fail?

Thanks,

M.MURALI kRISHNA

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-15 : 10:12:32

Havn't gone through his post in details, but one thing I know SQL throws an error when you try to reduce the column size, if there is any data that is greater than the reducing size.. take a look at this quick demo

CREATE TABLE tmp (id char(10) NOT NULL); -- will work
insert into tmp values ('xxxxxx');
ALTER TABLE tmp alter column id char(20); --will work
ALTER TABLE tmp alter column id char(5); -- won't work as there is data which exceeds 5 characters and is getting truncated

So I believe you'll not get error with Char datatype even by reducing it, untill it start disturbing the data.

Cheers
MIK

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-15 : 10:13:05

quote:
Originally posted by mmkrishna1919

Thanks Mik_2008 for clarifying NOT NULL issue.

And one more clarification is as "Bustaz Kool" said

Why altering(increasing size) of varchar column with NOT NULL is successfull where as for Char data type column it's fail?

Thanks,

M.MURALI kRISHNA

My conjecture is that this is because when you alter the length of CHAR column, you are materially changing the data stored.

If you had a column defined as CHAR(2), and you inserted a value of 'a' into that column, what really gets stored is the character a followed by a space; i.e., it is padded with trailing spaces.

Now when you increase the length of that column by changing it to say CHAR(5) you are materially affecting the data because every row will get padded with three more trailing spaces.

With varchar data type, when you increase the length of the column, the existing data remains unchanged.

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-05-16 : 01:33:02

Thanks james,Understood in case of CHAR and VARCHAR data types

But in case of altering TINYINT column to INT i think there is no change in the existing data as you said changes(adding extra spaces) in char data type column data.

while altering it has to increase only the allocated memory for column, then why alter from tinyint to int is not allowed?

M.MURALI kRISHNA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources