Re: Drop Primary Key with SQL/VBA

From: Chris2 (indanthrene.NOTVALID_at_GETRIDOF.yahoo.com)
Date: 04/03/04 

Date: Sat, 03 Apr 2004 00:54:09 GMT

"Gary Walter" <garylwpleasenospam@wamego.net> wrote in message
news:%23nKLj3LGEHA.744@TK2MSFTNGP09.phx.gbl...
>
> "gorden blom" <gordenblom@hotmail.com> wrote in message
> news:8c25c8c2.0404020426.47b58f1e@posting.google.com...
> > Hi,
> >
> > I want to drop the primary key from a table. I know I've got to delete
> > the constraint first. I'm working with Access 2000.
> >
> > DoCmd.RunSQL ("ALTER TABLE " & strTabel & " DROP Constraint ID;")
> > DoCmd.RunSQL ("ALTER TABLE " & strTabel & " DROP PRIMARY KEY;")
> >
> > The second line gives an error:
> >
> > Error 3293:
> >
> > Syntax error in ALTER TABLE statement.
> >
> Hi Gorden,
>
> Do you want to drop the Primary Key constraint on a field.....
> or drop the field that is the Primary Key?
>
> Either way, you will have to drop the constraint
> and you can only do that if you
>
> **know the name of the constraint.**
>
> ----------------------------------------
> If you "let Access name the constraint,"
> I don't know any way to find out what that
> name is (except maybe guess).
> ----------------------------------------
>
> If you create the constraint yourself,
> either in a DDL Create SQL
> or in a DDL Alter SQL,
> then you know the name to use in your Drop query.
>
> For example, in the following we end up
> knowing the name of the pk constraint
> so can drop it with following
>
> ALTER TABLE tblShipping
> DROP CONSTRAINT PK_CustomerID
>
>
> 1) -------------
> CREATE TABLE tblShipping
> (CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY,
> Address TEXT(50),
> City TEXT(50),
> State TEXT(2),
> Zip TEXT(10))
> ------------
>
> 2) ------------------------------
> CREATE TABLE tblShipping
> (CustomerID INTEGER,
> Address TEXT(50),
> City TEXT(50),
> State TEXT(2),
> Zip TEXT(10))
>
> ALTER TABLE tblShipping
> ALTER COLUMN CustomerID INTEGER
> CONSTRAINT PK_CustomerID PRIMARY KEY
> -----------------------------

  Try:

ALTER TABLE tblShipping DROP
   CONSTRAINT PK_CustomerID

>
> Then, once the constraint is dropped,
> then you can drop the field if that was
> what you wanted.
>
>

Relevant Pages

  • SQL Server confused about primary keys. So am I.
    ... We are generating a script to convert a client's database to Sql ... ALTER TABLE dbo.Categories ADD ... CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.server)
  • Re: Alter table help
    ... alter table table_name add constraint constraint_name primary key ... > "Tom Pennington" wrote in message ... >> Okay, I'm trying to modify a tables primary key, actually, I'm trying to ...
    (microsoft.public.sqlserver.programming)
  • Re: Database design question
    ... say we have document and we need to specify permissions to the ... Is there anyway we can have constraint like ... alter table DocumentPermission ... You also need a primary key for DocumentPermission. ...
    (comp.databases.theory)
  • Re: Drop Primary Key with SQL/VBA
    ... you will have to drop the constraint ... >> or in a DDL Alter SQL, ... >> CREATE TABLE tblShipping ...
    (microsoft.public.access.queries)
  • Re: unknown symbol in ER diagram
    ... main_ID INTEGER NOT NULL PRIMARY KEY ... CONSTRAINT fk__sub1 FOREIGN KEY ... REFERENCES Main ... ALTER TABLE Sub1 ADD CONSTRAINT ch__sub1 ...
    (microsoft.public.access.tablesdbdesign)
Loading