Trying to remove Primary Key constraint

[SimDuck]

Hi, I have a table where I wan't to remove the Primary key. I run the sql command of "ALTER TABLE table_name DROP CONSTRAINT Primary_key"

 

The error I get is "CHECK constraint 'Primary_Key' does not exist.

 

When I added the Primary key, I used the sql command of "ALTER TABLE table_name ALTER COLUMN column_name varchar(30) PRIMARY KEY"

 

This added fine because I now cannot drop the column.

 

I am trying to find the constraint name so I can delete it. I have run code to try to find the constraint name. This is what I have run:

 

Dim cs As Constraint

For Each cs In dsBudget.Tables.Item(0).Constraints

Console.WriteLine(cs.ConstraintName)

Next cs

 

The problem I am facing is that this code cannot pickup any constraints. If I use code to find a primary key, the array returns -1 indicating that there is no primary key.

 

The table or column definitely has a primary key.

 

My question is, if I can't find out the name of the constraint, how can I remove it?

 

Thank you for your assistance

[eramgarden]

Can you see the constraint when you open the table in design mode from EM?

[PlausiblyDamp]

The code you have posted will display any constraints on the DataSet, not on the database table. If you are using MS SQL then

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE  =  'PRIMARY KEY'
AND TABLE_NAME = '</pre><table name here>'
</

will return the name of the primary key for a given table.

[SimDuck]

I can see the constraint when I create a dataset & view the xml schema in design mode. It has a key next to afield.

 

I ran this command "SELECT CONSTRAINT_NAME FROM Account_Details.TABLE_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'Account_Codes'"

 

and got this error "The Microsoft Jet database engine cannot find the input table or query 'TABLE_CONSTRAINTS'. Make sure it exists and that its name is spelled correctly."

 

I'm sorry, I failed to mention that I am using MS Jet database engine to access my Access 2000 database.

 

I changed "INFORMATION_SCHEMA" to the database name because an error said it couldn't find table INFORMATION_SCHEMA

 

Is there another thing I could try?

 

Thank you for your assistance