How to remove a Primary Key you don't know?

Hello Guys,

I need some help with a question I've been struggling to solve all day. I'm new to Transact-SQL and thus tried to solve my little problem by reading the language reference, but I didn't find what I was looking for.

Here's the scenario: I want to remove a table's primary key and replace it with a new one. Removing the pk is usually done with:

1) ALTER TABLE tablename DROP CONSTRAINT constraint_name; GO

Unfortunatley, I don't know constraint_name and the simple MySQL command

ALTER TABLE tablename DROP PRIMARY KEY

doesn't seem to work. To get constraint_name I made a query:

2) SELECT name from sys.key_constraints where parent_object_id = (select object_id from sys.tables where name = 'tablename')

Now, here's the core of my problem: I need to link statements 1) and 2) and tried

ALTER TABLE tablename DROP CONSTRAINT (SELECT name from sys.key_constraints where parent_object_id = (select object_id from sys.tables where name = 'tablename'))

but that doesn't, I get a syntax error.

Do you have a clue? I'm sure its a simple problem for an experienced programmer, but I don't get it. Or do you know another way of removing the primary key without having to state constraint_name explicitely?

Any help is appreciated!

Thanks a bunch,

adunak

You need to use dynamic SQL to execute the ALTER TABLE statement if you want to parameterize the constraint name. Most of the DDL statement parameters in TSQL cannot be parameterized by using variables or expressions. So do below instead:

declare @.const nvarchar(200)

set @.const = QUOTENAME((

select kc.name

from sys.key_constraints as kc

where kc.parent_object_id = object_id('tablename') and kc.type = 'PK'

))

exec('ALTER TABLE tablename DROP CONSTRAINT ' + @.const)

|||

Hi Umachandar,

your hint worked well - thanks a lot for helping me out :-)

Adunak

How to reference the primary key of a newly added record in trigger?

Please help me somebody solve my problem with my first :o trigger:

ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
INSERT INTO partner (name) SELECT name FROM deleted
UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted)


*** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?I'd use:ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
BEGIN
INSERT INTO partner (name)
SELECT name FROM deleted

UPDATE o
SET id_partner= i.id_partner
FROM dbo.invoice AS o
INNER JOIN deleted AS d
ON (d.id_partner = o.id_partner)
LEFT OUTER JOIN inserted AS i
ON (i.id_partner = o.id_partner)
ENDThis trigger takes a rather perverse view of the universe, allowing for the possiblity that some low-down, high-smelling, bundle-of-dung might possibly change the value of the id_partner column. The invoice table might or might not like that, since the trigger would then NULL out the invoice.id_partner column!

-PatP

How to recover the primary sevrer in a cluster?

Hello, everyone:
The two SQL Server 2k (SP4) are running on W2K(SP4) in clustered A/A mode. The seconary server took over successfuly as the primary server crached. How to recover the primary sevrer?
Thanks a lot.Few articles to help:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx and http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part4/c1261.mspx

HTH

How to read just inserted auto incremented primary key to use it as parameter?

Hi. After inserting data (new row) by using DetailsView control, how to read auto incremented primary key (identity) of this new row from sql database to use it as parameter passed to stored procedure?

Or in other way. I need to insert new row (let's call them parent row) in one table by using DetailsView control. This is quite easy. But after inserting I need to read primary key of this new row to insert new child row in other table. Both tables are in relation. How to do this? Any suggestions? Pawel.

Hi Pawelek,

Since the ID (primary key) is configured as an auto incremented field, the last row inserted will always have the maximum number.

You could write a stored procedure which selects the maximum ID number ( SELECT max(ID) from ParentRowsTable ) and returns it.
Then in your code you can execute the SP after you insert a row and you get the max. ID of the parent rows.

Greets,

Wim

|||Hi. I'm afraid that this is a risky business. What if in the same time some other people will insert new row which becomes row with maximum number? Is it possible such situation? Regards. Pawel.|||

Hi. you can return the@.@.IDENTITY

INSERT INTO [dbo].[table] (name, phone)VALUES ("test", "tttt")Return@.@.IDENTITY

|||I would recommend using SCOPE_IDENTITY() instead of @.@.IDENTITY. The former maintains the scope. Check out BOL for more info.