Trying to remove Primary Key constraint

S

SimDuck

Member
Joined
Oct 25, 2004
Messages
21
Hi, I have a table where I want 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 cant find out the name of the constraint, how can I remove it?

Thank you for your assistance
 
The code you have posted will display any constraints on the DataSet, not on the database table. If you are using MS SQL then
Code: 
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE  =  PRIMARY KEY
AND TABLE_NAME = <TABLE NAME HERE>
will return the name of the primary key for a given table.
 
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."

Im 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 couldnt find table INFORMATION_SCHEMA

Is there another thing I could try?

Thank you for your assistance
 
Back
Top