ansaurus

Question

MS ACCESS How to drop a column with primary key.

Answer 1

+1 A:

You need to remove the primary key index on the table first in one query:

DROP INDEX PrimaryKey ON Table1

Then you can remove the column in a second query:

ALTER TABLE Table1 DROP COLUMN id

Alistair Knock 2009-10-30 10:31:58

i am getting an error message no such index primarykey on table1

tksy 2009-10-30 11:14:44

You need to remove any relationship the table is participating in before your remove the PK.

David-W-Fenton 2009-10-30 23:44:26

@David W. Fenton: Don't you rather mean "You need to remove any relationship the PK is participating in before your remove the PK"? A SQL foreign key can reference a UNIQUE constraint that is not a PRIMARY KEY. A MS Access 'relationship' doesn't even require a unique constraint (don't believe the message you see when you try to save a table without a PK created via the designer, it lies,it lies).

onedaywhen 2009-11-02 14:38:12

Answer 2

A:

You can get the name of the index in a number of ways.

Dim RS As ADODB.Recordset

    Set RS = CurrentProject.Connection.OpenSchema _
        (12, Array(Empty, Empty, Empty, Empty, "Table1")) ''12=adSchemaIndexes
    RS.Filter = "PRIMARY_KEY = True"
    If Not RS.EOF Then
        Debug.Print RS.Fields("Index_Name")
    End If
End Sub

More here http://stackoverflow.com/questions/1649049/what-is-the-name-of-the-violating-unique-index-constraint-in-dao-ms-access

Remou 2009-10-30 12:26:55

ansaurus

tags:

views:

answers:

MS ACCESS How to drop a column with primary key.

related questions