Remove or disable primary key on table  
Author Message
njnyposp





PostPosted: Fri Feb 20 14:31:02 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table



WHERE TABLE_NAME = 'Form22_5' AND CONSTRAINT_TYPE = 'primary key'



When it gets to the alter table statement it issues the following error:
Server: Msg 3728, Level 16, State 1, Line 4
'@pkname' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 4
Could not drop constraint. See previous errors.

How do I programatically remove a primary key from a table?
Or how do I copy a record into the same table that has a primary key WITH
OUT specifying the fields?
For example INSERT INTO Form22_5 SELECT * FROM Form22_5 WHERE formID =
@formID

SQL Server47  
 
 
Aaron





PostPosted: Fri Feb 20 14:31:02 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table You can't use a variable here.



However, before implementing, please read
http://www.sommarskog.se/dynamic_sql.html

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/








> WHERE TABLE_NAME = 'Form22_5' AND CONSTRAINT_TYPE = 'primary key'


>
> When it gets to the alter table statement it issues the following error:
> Server: Msg 3728, Level 16, State 1, Line 4

> Server: Msg 3727, Level 16, State 1, Line 4
> Could not drop constraint. See previous errors.
>
> How do I programatically remove a primary key from a table?
> Or how do I copy a record into the same table that has a primary key WITH
> OUT specifying the fields?
> For example INSERT INTO Form22_5 SELECT * FROM Form22_5 WHERE formID =

>
>


 
 
John





PostPosted: Fri Feb 20 15:05:23 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table Hi

ALTER TABLE does not take a parameter as the constraint name. Try using
dynamic SQL.

John





> WHERE TABLE_NAME = 'Form22_5' AND CONSTRAINT_TYPE = 'primary key'


>
> When it gets to the alter table statement it issues the following error:
> Server: Msg 3728, Level 16, State 1, Line 4

> Server: Msg 3727, Level 16, State 1, Line 4
> Could not drop constraint. See previous errors.
>
> How do I programatically remove a primary key from a table?
> Or how do I copy a record into the same table that has a primary key WITH
> OUT specifying the fields?
> For example INSERT INTO Form22_5 SELECT * FROM Form22_5 WHERE formID =

>
>



 
 
Joe





PostPosted: Fri Feb 20 19:30:23 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table >> How do I programatically remove a primary key from a table? <<

How could you ever****up so badly that this would even be a question?
Primary keys are little add-ons in a dat model; they are foundations.
Didn't you think about this when you did the data model?

>> Or how do I copy [sic] a record [sic] into the same table that has a
primary key WITHOUT specifying the fields [sic]? <<

You need to read a book on SQL and RDBMS. Rows are not records; fields
are not columns; tables are not files. When you **INSERT** a **ROW**
into a table, it has to have **COLUMNS** by defintion.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.hide-link.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
Kirk





PostPosted: Fri Feb 20 20:51:30 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table Wow, Celko,

The guy had a question, I would think it would be in everyones best interest
to politely answer it, or not post a reply at all.

I assume you have never had to pick up someone elses code and just
make_it_work, regardless of what architectural problems it might have had.
And of course you never had to learn anything a little at a time. You were
clearly torn from your mothers womb fully certified and holding 2 masters
degrees in each hand.

Polite my good man. Polite....

Kirk Graves
KRGIT Software



> >> How do I programatically remove a primary key from a table? <<
>
> How could you ever****up so badly that this would even be a question?
> Primary keys are little add-ons in a dat model; they are foundations.
> Didn't you think about this when you did the data model?
>
> >> Or how do I copy [sic] a record [sic] into the same table that has a
> primary key WITHOUT specifying the fields [sic]? <<
>
> You need to read a book on SQL and RDBMS. Rows are not records; fields
> are not columns; tables are not files. When you **INSERT** a **ROW**
> into a table, it has to have **COLUMNS** by defintion.
>
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.hide-link.com/ ***
> Don't just participate in USENET...get rewarded for it!


 
 
Marc





PostPosted: Fri Feb 20 21:17:44 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table If you have something useful that will help solve my problem then post it
else shut up.



> >> How do I programatically remove a primary key from a table? <<
>
> How could you ever****up so badly that this would even be a question?
> Primary keys are little add-ons in a dat model; they are foundations.
> Didn't you think about this when you did the data model?
>
> >> Or how do I copy [sic] a record [sic] into the same table that has a
> primary key WITHOUT specifying the fields [sic]? <<
>
> You need to read a book on SQL and RDBMS. Rows are not records; fields
> are not columns; tables are not files. When you **INSERT** a **ROW**
> into a table, it has to have **COLUMNS** by defintion.
>
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.hide-link.com/ ***
> Don't just participate in USENET...get rewarded for it!


 
 
Marc





PostPosted: Fri Feb 20 21:27:16 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table I should have mentioned earlier - but ALTER TABLE Form22_5 DROP CONSTRAINT
PK_Form22_5 also generates the same error message (i.e. PK_Form22_5 is not a
constraint).



> You can't use a variable here.
>

>
> However, before implementing, please read
> http://www.sommarskog.se/dynamic_sql.html
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
>
>




INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> > WHERE TABLE_NAME = 'Form22_5' AND CONSTRAINT_TYPE = 'primary key'


> >
> > When it gets to the alter table statement it issues the following error:
> > Server: Msg 3728, Level 16, State 1, Line 4

> > Server: Msg 3727, Level 16, State 1, Line 4
> > Could not drop constraint. See previous errors.
> >
> > How do I programatically remove a primary key from a table?
> > Or how do I copy a record into the same table that has a primary key
WITH
> > OUT specifying the fields?
> > For example INSERT INTO Form22_5 SELECT * FROM Form22_5 WHERE formID =

> >
> >
>
>


 
 
Marc





PostPosted: Fri Feb 20 21:32:50 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table Could you provide an example - do you mean: EXEC('ALTER TABLE Form22_5 DROP
CONSTRAINT PK_Form22_5')



> Hi
>
> ALTER TABLE does not take a parameter as the constraint name. Try using
> dynamic SQL.
>
> John
>




INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> > WHERE TABLE_NAME = 'Form22_5' AND CONSTRAINT_TYPE = 'primary key'


> >
> > When it gets to the alter table statement it issues the following error:
> > Server: Msg 3728, Level 16, State 1, Line 4

> > Server: Msg 3727, Level 16, State 1, Line 4
> > Could not drop constraint. See previous errors.
> >
> > How do I programatically remove a primary key from a table?
> > Or how do I copy a record into the same table that has a primary key
WITH
> > OUT specifying the fields?
> > For example INSERT INTO Form22_5 SELECT * FROM Form22_5 WHERE formID =

> >
> >
>
>
>


 
 
John





PostPosted: Sat Feb 21 03:04:51 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table Hi

Aaron's reply gave an example although it does not appear on my news server

> You can't use a variable here.
>

>
> However, before implementing, please read
> http://www.sommarskog.se/dynamic_sql.html
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/

John



> Could you provide an example - do you mean: EXEC('ALTER TABLE Form22_5
DROP
> CONSTRAINT PK_Form22_5')
>


> > Hi
> >
> > ALTER TABLE does not take a parameter as the constraint name. Try using
> > dynamic SQL.
> >
> > John
> >




> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> > > WHERE TABLE_NAME = 'Form22_5' AND CONSTRAINT_TYPE = 'primary key'


> > >
> > > When it gets to the alter table statement it issues the following
error:
> > > Server: Msg 3728, Level 16, State 1, Line 4

> > > Server: Msg 3727, Level 16, State 1, Line 4
> > > Could not drop constraint. See previous errors.
> > >
> > > How do I programatically remove a primary key from a table?
> > > Or how do I copy a record into the same table that has a primary key
> WITH
> > > OUT specifying the fields?
> > > For example INSERT INTO Form22_5 SELECT * FROM Form22_5 WHERE formID
=

> > >
> > >
> >
> >
> >
>
>


 
 
Chris2





PostPosted: Sat Feb 21 16:46:15 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table Was the error message returned when the literal CONSTRAINT name was used the
same as when the variable CONSTRAINT name was used?




> I should have mentioned earlier - but ALTER TABLE Form22_5 DROP CONSTRAINT
> PK_Form22_5 also generates the same error message (i.e. PK_Form22_5 is not
a
> constraint).
>


> > You can't use a variable here.
> >

> >
> > However, before implementing, please read
> > http://www.sommarskog.se/dynamic_sql.html
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
> >
> >




> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> > > WHERE TABLE_NAME = 'Form22_5' AND CONSTRAINT_TYPE = 'primary key'


> > >
> > > When it gets to the alter table statement it issues the following
error:
> > > Server: Msg 3728, Level 16, State 1, Line 4

> > > Server: Msg 3727, Level 16, State 1, Line 4
> > > Could not drop constraint. See previous errors.
> > >
> > > How do I programatically remove a primary key from a table?
> > > Or how do I copy a record into the same table that has a primary key
> WITH
> > > OUT specifying the fields?
> > > For example INSERT INTO Form22_5 SELECT * FROM Form22_5 WHERE formID
=

> > >
> > >
> >
> >
>
>


 
 
Marc





PostPosted: Sat Feb 21 21:47:34 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table Yes - the error returned also when I used the literal CONSTRAINT name -
however when I droped the table and added again I was able to drop the
primary key with no problem. I have no idea why it works now.



> Was the error message returned when the literal CONSTRAINT name was used
the
> same as when the variable CONSTRAINT name was used?
>
>


> > I should have mentioned earlier - but ALTER TABLE Form22_5 DROP
CONSTRAINT
> > PK_Form22_5 also generates the same error message (i.e. PK_Form22_5 is
not
> a
> > constraint).
> >


> > > You can't use a variable here.
> > >

> > >
> > > However, before implementing, please read
> > > http://www.sommarskog.se/dynamic_sql.html
> > >
> > > --
> > > Aaron Bertrand
> > > SQL Server MVP
> > > http://www.aspfaq.com/
> > >
> > >
> > >
> > >




> > INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> > > > WHERE TABLE_NAME = 'Form22_5' AND CONSTRAINT_TYPE = 'primary key'


> > > >
> > > > When it gets to the alter table statement it issues the following
> error:
> > > > Server: Msg 3728, Level 16, State 1, Line 4

> > > > Server: Msg 3727, Level 16, State 1, Line 4
> > > > Could not drop constraint. See previous errors.
> > > >
> > > > How do I programatically remove a primary key from a table?
> > > > Or how do I copy a record into the same table that has a primary key
> > WITH
> > > > OUT specifying the fields?
> > > > For example INSERT INTO Form22_5 SELECT * FROM Form22_5 WHERE
formID
> =

> > > >
> > > >
> > >
> > >
> >
> >
>
>


 
 
Marc





PostPosted: Sat Feb 21 21:52:21 CST 2004 Top

SQL Server Developer >> Remove or disable primary key on table I figured out a way to make a copy of a row into the same table that has a
primary key without specifying the columns. Here is how I did it if any one
wants to know.

CREATE PROCEDURE [dbo].[sp_Utility_CopyPkRow]
@tableName varchar(50),
@pKey varchar(50),
@pkValue varchar(20),
@newPkValue varchar(20)
AS







RETURN

RETURN

RETURN


SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME
WHERE







ELSE
BEGIN


BEGIN

SET ROWCOUNT 1

SET ROWCOUNT 0

END



END


GO





> WHERE TABLE_NAME = 'Form22_5' AND CONSTRAINT_TYPE = 'primary key'


>
> When it gets to the alter table statement it issues the following error:
> Server: Msg 3728, Level 16, State 1, Line 4

> Server: Msg 3727, Level 16, State 1, Line 4
> Could not drop constraint. See previous errors.
>
> How do I programatically remove a primary key from a table?
> Or how do I copy a record into the same table that has a primary key WITH
> OUT specifying the fields?
> For example INSERT INTO Form22_5 SELECT * FROM Form22_5 WHERE formID =

>
>