Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Primary Key

Quote Reply
Primary Key
   

Hi,

How can I get DBMan SQL 2.0.3 to automatically generate a new number for the primary key?

DBMan SQL 1 has $db_supply_key = 1 to generate a new number behind the scenes. Is there a similar setting in 2.0.3?

I have created a column in my table called "contact_id" which is the primary key but I don't know how to get DBMan SQL 2.0.3 to insert the next number into the column.

Thank you

Simon.
Quote Reply
Re: [jai] Primary Key In reply to
Have you tried making the column auto-increment?
Quote Reply
Re: [RedRum] Primary Key In reply to
Hi,

I saw auto-increment in the Help pages but that option doesn't seem to show up in my table section. Any ideas??

Thank you.

Simon.
Quote Reply
Re: [jai] Primary Key In reply to
It should be 'extra' property

TheStone.

B.
Quote Reply
Re: [TheStone] Primary Key In reply to
If I create a new table the "Extas" property is available but it doesn't appear for existing tables. Do I have to start from scratch?

Thanks

Simon.
Quote Reply
Re: [jai] Primary Key In reply to
You can not alter or add a field as auto-increment for an existing table.

TheStone.


B.
Quote Reply
Re: [TheStone] Primary Key In reply to
I've imported my dbman flatfile db to sql and I've set the key field to auto-increment. I had this same field set to auto-increment in dbman (flatfile db). However, when I add a new record to dbman sql the field isn't auto-incremented? Does it have to do with the fact that I've imported records so the counter shouldn't begin at 1???? Frown
Reena
Quote Reply
Re: [Reena0330] Primary Key In reply to
Is it an auto-incresement field? You can check it by using Admin - Tables - SQL Query to execute 'describe table_name'


TheStone.

B.
Quote Reply
Re: [TheStone] Primary Key In reply to
It's not coming up as auto-increment:

recordno varchar(4) PRI


But I remember setting it to auto-increment. How can I fix this? Unsure
Reena
Quote Reply
Re: [TheStone] Primary Key In reply to
Okay so the top of my .def file looks like this:

Code:
{
'ai' => '',
'cols' => {
'recordno' => {



do I need to change it to???? ->

Code:
{
'ai' => 'recordno',
'cols' => {
'recordno' => {
Actually this doesn't work...I tried it Unsure
Reena

Last edited by:

Reena0330: Aug 9, 2002, 2:24 PM
Quote Reply
Re: [Reena0330] Primary Key In reply to
Yes, you should change def file and alter this table by using SQL query:
Code:
alter table table_name change recordno recordno int NOT NULL AUTO_INCREMENT
Btw, can you send me your dbman flatfile at [url? so I'll have a look at it to figure out what wrong with import script.

Thanks,
TheStone.

B.

Last edited by:

TheStone: Jul 16, 2003, 1:10 PM
Quote Reply
Re: [TheStone] Primary Key In reply to
Hi I changed:

'ai' => '',
'cols' => {


to:

'ai' => 'recordno',
'cols' => {



and I did the following sql query:

alter table customerCases change recordno recordno int NOT NULL AUTO_INCREMENT



But still no change. I received the following message after submitting the query above:

Rows affected: -2



Am I doing something wrong?
Reena
Quote Reply
Re: [Reena0330] Primary Key In reply to
Are you connecting mysql database? if so, double check on table name, field name.


Code:


alter table table_name change old_field_name new_field_name int NOT NULL AUTO_INCREMENT


B.

Last edited by:

TheStone: Aug 9, 2002, 2:56 PM
Quote Reply
Re: [TheStone] Primary Key In reply to
When you say double check on field and table name..what do u mean? Well, I went online to check if the command syntax was correct and everything looks good. I also checked if I am inserting the correct field and table name and that looks good also

ALTER TABLE customerCases CHANGE recordno recordno INT NOT NULL AUTO_INCREMENT

...but still getting the same message.

Rows affected: -2

Is there any other way to do this?
Reena
Quote Reply
Re: [Reena0330] Primary Key In reply to
Can you send me the admin info to access DBMan SQL in private? I'll have a look at it closer

B.
Quote Reply
Re: [TheStone] Primary Key In reply to
Dbman SQL is on an internal server so there's no way for you to access it. Is there any other way I can check if I'm typing in the correct information?
Reena
Quote Reply
Re: [Reena0330] Primary Key In reply to
Let try run the command at mysql prompt.

B.
Quote Reply
Re: [TheStone] Primary Key In reply to
Okay so here's what I did:

mysql>
mysql>
mysql> ALTER TABLE customerCases CHANGE recordno recordno int NOT NULL AUTO_INC
REMENT
->



I'm not too familiar with mysql so am I doing this right? What do I type in at the "->"
Reena
Quote Reply
Re: [Reena0330] Primary Key In reply to
You should type:

mysql> ALTER TABLE customerCases CHANGE recordno recordno int NOT NULL AUTO_INCREMENT;

B.

Last edited by:

TheStone: Aug 9, 2002, 4:15 PM
Quote Reply
Re: [TheStone] Primary Key In reply to
Finally, I was able to change the column attributes! Sly yeaahh!


Your query returned 34 rows.

recordno int(11) PRI auto_increment

So I deleted all the records from my database and I started from scratch to see if the records would auto increment, but still nothing. Unsure If the auto-incr option is selected then the field should automatically get filled in with a record number right? So what should I do now?[/code]Reena