SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Altering Primary Key
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mmkrishna1919
Yak Posting Veteran

India
94 Posts

Posted - 05/13/2013 :  05:39:01  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/13/2013 :  06:45:24  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 05/13/2013 :  08:36:16  Show Profile  Reply with Quote
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;
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 05/13/2013 :  12:59:14  Show Profile  Reply with Quote
I ran this script and I get a failure after every attempt to alter the existing primary key column:
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


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

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 05/13/2013 :  14:45:32  Show Profile  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 05/13/2013 :  19:06:23  Show Profile  Reply with Quote
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)
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
94 Posts

Posted - 05/15/2013 :  06:49:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2013 :  07:08:00  Show Profile  Reply with Quote
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
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
94 Posts

Posted - 05/15/2013 :  08:36:47  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/15/2013 :  09:39:51  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 05/15/2013 09:40:28
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
94 Posts

Posted - 05/15/2013 :  10:00:42  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/15/2013 :  10:12:32  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 05/15/2013 10:29:21
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 05/15/2013 :  10:13:05  Show Profile  Reply with Quote
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.

Edited by - James K on 05/15/2013 10:13:54
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
94 Posts

Posted - 05/16/2013 :  01:33:02  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000