1

There is topic on StackOverflow: Remove Primary Key in MySQL Where it is described how to unassigned primary key from table's column. But this method did not work in my case.

I did use the code written in the topic above, for this extremely simple procedure, but still receive errors. Please help. Here is a code:

ALTER TABLE [BOM_V164_MK1A_PLM-000780855_02_D02102018_T10:50:56] 
   DROP PRIMARY KEY;

Here is the error I get:

enter image description here

Thanks everyone in advance.

Ivan Savchenco
  • 75
  • 8
  • if you are using msaccess than check this out (https://stackoverflow.com/questions/1648974/ms-access-how-to-drop-a-column-with-primary-key) – Tarun. P Oct 02 '18 at 12:28
  • If Backend is MySQL, use a passthrough-query and back-ticks instead of square-brackets to surround table-name. – ComputerVersteher Oct 02 '18 at 12:35
  • The link https://stackoverflow.com/questions/1648974/ms-access-how-to-drop-a-column-with-primary-key you gave me also does not work: https://ibb.co/ekxrpK – Ivan Savchenco Oct 02 '18 at 13:02
  • You have to know the name of the index (not the column) that is used as the primary key (that is probably named like `[PK_BOM_V164_MK1A_PLM-000780855_02_D02102018_T10:50:56]`and drop that index from the table. Check the indexes window to get the index name. – Wolfgang Kais Oct 03 '18 at 00:13
  • Used this https://www.devhut.net/2014/11/20/ms-access-list-table-indexes/ function to find index. After I found index, I dropped it easily. So basically everything is solved:) Many thank to everyone's help! Now I need to mark the question as solved, and as I am a new guy I don't know how to do this... – Ivan Savchenco Oct 03 '18 at 06:03

1 Answers1

0

You can drop the index specifically:

ALTER TABLE [BOM_V164_MK1A_PLM-000780855_02_D02102018_T10:50:56] 
   DROP Constraint [PrimaryKey];
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • For dropping indexes, I prefer DROP INDEX _indexname_ ON _table_ over an alter table statement. Both work, but `DROP INDEX` immediately makes you realize you're dropping an index, `DROP CONSTRAINT` can be any constraint. – Erik A Oct 02 '18 at 12:40
  • Sorry, but DROP INDEX also didn't work in my case: https://ibb.co/ekxrpK :( – Ivan Savchenco Oct 02 '18 at 13:03
  • 1
    Your original syntax seems not to be supported in Access. My syntax works - but, of course, only of you know the name of the primary key index which, in Access, typically is _PrimaryKey_. – Gustav Oct 02 '18 at 13:37
  • "typically is PrimaryKey".... Unfortunately it does not work in my Access Aplication... I am extremely disappointed as I have a lot of much more difficult tasks and I can't do this little simple thing....((((( – Ivan Savchenco Oct 02 '18 at 14:05
  • 1
    As others have stated, you need to know what the index is called - try using the function listed here https://www.devhut.net/2014/11/20/ms-access-list-table-indexes/ – Minty Oct 02 '18 at 14:15
  • 1
    @Minty: Yep, and then use DAO and the TableDef object to remove the index. – Gustav Oct 02 '18 at 15:51
  • ALTER TABLE [BOM_V164_MK1A_PLM-000780855_02_D02102018_T10:50:56] DROP Constraint [PrimaryKey]; Works on my Home PC. Does not work at work. Tomorrow I will try to find out what is the Index name. Many thanks for your help! – Ivan Savchenco Oct 02 '18 at 18:41
  • Used this https://www.devhut.net/2014/11/20/ms-access-list-table-indexes/ function to find index. After I found index, I dropped it easily. So basically everything is solved:) Many thank to everyone's help! Now I need to mark the question as solved, and as I am a new guy I don't know how to do this... – Ivan Savchenco Oct 03 '18 at 06:03
  • Left of the answer, there should be a sign to mark if answered. Also, mark up the comment from @Minty (as I have done). – Gustav Oct 03 '18 at 07:11