Primary key problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Primary key problem

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2006-08-31 : 05:44:41

I have one table tab1 with column col1 varchar (10) as a primary key
I want to increase the size of this colum .
So I have written the following query

if(select length from syscolumns, sysobjects
where syscolumns.id = sysobjects.id and sysobjects.name=’tab1’ and
syscolumns.name='col1’ ) <> 24
ALTER TABLE tab1 ALTER COLUMN col1 char(24)
go

ALTER TABLE [dbo].[col1] ADD CONSTRAINT [col1_pk] PRIMARY KEY NONCLUSTERED
(
col1
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

But when I am trying to alter the table I got following error

Server: Msg 5074, Level 16, State 8, Line 4
The object 'tab1s_pk' is dependent on column col1.
Server: Msg 4922, Level 16, State 1, Line 4
ALTER TABLE ALTER COLUMN col1 failed because one or more objects access this column.

So please tell me how I can check weather that CONSTRAINT is available or not and if it is available then how I can drop using query

SQLTeam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-31 : 05:50:56

why don't you do the changes via Enterprise Manager, it will take care of the constraints issue for you. There is also an option to save the changes script to file.

shubhada
Posting Yak Master

117 Posts

Posted - 2006-08-31 : 06:03:47

no i don't want to use Enterprise maneger...
I want query only to perform all these operation....

SQLTeam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-31 : 06:09:20

then make use of this object INFORMATION_SCHEMA.CHECK_CONSTRAINTS

Why don't you check out the Enterprise Manager "save changes to script" option and see how EN drop the constraints ? You can save the script and run the script in Query Analyzer.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-31 : 06:10:39

I am not saying you have to use Enterprise Manager to perform the changes but just to see (in Query script) how would EM do it and learn from it.

shubhada
Posting Yak Master

117 Posts

Posted - 2006-08-31 : 06:16:26

I am not getting this How i can do this?

SQLTeam

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-31 : 06:31:43

hi
u have to drop that constraints first and then alter the column with setting PK to it

hope that help you lot

Thanks

shubhada
Posting Yak Master

117 Posts

Posted - 2006-08-31 : 06:36:30

ya...but how i can drop that constraint ..
i want that query..
I have no iead where this constraint is stored.

SQLTeam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-31 : 09:45:12

You can lists the constraints of a table using :

select	object_name(constid)
from 	sysconstraints
where 	id = object_id('table_name')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources