Difficulty with auto column and primary key errors in designer

Hi John,

If you have an auto increment column in a table, that column must be the one and only primary key column. That makes sense, since your auto increment column identifies each row uniquely and there is no need for an extra column.

The reason why you are getting this error is probably because there is already a primary key on your table and your alter table statement tries to add one more. Is that correct? You need to drop the index first and then perform the after table, or drop the table, and then recreate it.

Let me know if this helps…

Best regards,
Peter

PS The Data Designer needs some more attention! At this time I think, if possible, the best way to use the Data Designer is drop your table and recreate it…

Hi John,

Is this topic solved?

Thanks,
Peter

Hey Peter,

Thanks for getting back to me faster than anyone before, really sorry I wasn’t able to return the favor haha. After dropping the index I still got this error:

The following ALTER TABLE statement failed
ALTER TABLE <code>startracker_tutors</code> MODIFY COLUMN <code>ID</code> int(10) NOT NULL AUTO_INCREMENT;
ALTER TABLE <code>startracker_tutors</code> ADD PRIMARY KEY (<code>ID</code>);
Incorrect table definition; there can be only one auto column and it must be defined as a key

While trying to make it a primary key.

If I take out the “auto_increment” to first make the ID column a Key it returns this error:

The following ALTER TABLE statement failed
ALTER TABLE <code>startracker_tutors</code> MODIFY COLUMN <code>ID</code> int(10) NOT NULL;
ALTER TABLE <code>startracker_tutors</code> ADD PRIMARY KEY (<code>ID</code>);
Duplicate entry '0' for key 'PRIMARY'

Currently no column is set as ‘Key’ and I’m not able to set one, so I’m taking your advice and rebuilding the entire table.

Since I’m building a tutor-pupil tracking database I’m taking my time to design it properly and preempt any future needs that may arise, therefor I’ve installed Microsoft Access to create the outline first and then re-create it in wpda. I don’t suppose it’s possible to use a mysql client in combination with wpda publisher?

I’m also looking for whether I can merge the wp-user table with my users table and allow users to manage their own contact details. This would eliminate the need for users to be added by an administrator. But I’m not quite sure if this is a good idea.

Thanks so much for the plugin man, learned a ton!

You know, I’ve noticed something really weird. I didn’t know that each table could have only one Primary Key so I created the pupil table with multiple PK’s… Now that I know that shouldn’t be possible I think it’s strange.

When reading my Database in MySQL workbench it says this:

Table: startracker_pupils
Columns:
ID int(11) AI PK 
date_created datetime 
date_modified datetime 
name varchar(64) PK 
rate varchar(10) 
wpID int(11)

Should that be possible?

Hi John,

You can merge the wp_users table! If you plan to built a Data Project, you can even create a lookup to the wp_users table to allow your admin user to select the WP user from a listbox.

You can also use the WP user id in your where clause (to show only user related data) and use it to restrict access (a student profile page for example). See the first link below $$USERID$$ environment variable.

You can find more information about these topics here:
https://wpdataaccess.com/docs/documentation/data-projects/managing-roles-and-user-access/
https://wpdataaccess.com/docs/documentation/data-projects/video-tutorials/#fine-tuning

BTW, this is the more complex stuff! ??

Regarding your create table script, a primary key can consist of more than one column. So this would work:
primary key (ID,name)

However, if your ID column is an auto increment column, the name column does not make the prikary key “more unique”. The ID already is unique. So, yes, this is possible, but does not make sense if your ID column is an auto increment.

Hope this helps!

Best regards,
Peter

Peter/John:
With regard to the primary key (ID, name) and the assumption that you want “name” to be unique, why not simply declare a UNIQUE INDEX on name?

Just my $0.02! (USD)
Dave

@peterschulznl Oke, I figured out how to solve it all by just using PhPmyAdmin, it seems a little easier to make out what the heck I’m doing when designing schemas ?? Also read up a bit on composite keys. I think I understand that now though I don’t know exactly how to apply them.

Using the $$USERID$$ seems easy enough, I haven’t tested it yet but the where clause is easy to grasp.

The lookup is also a very nice feature but as I understand it this lookup does not translate to the schema am I correct? As in, the relationship is stored in WPDA and not in my schema so I couldn’t for example see or edit the lookup or other relationships in PHPmyadmin?

What I would really like is for the admin to not have to assign a WP ID to every tutor, pupil, and parent tables. I realize the effort this takes for me to learn and apply is radically disproportionate to any gain anyone might get with 2 tutors and 3 pupils in total but since university has closed I’m all in to creating this amazing dashboard.

@dnuttall Hey thanks for the suggestion, I don’t want name to be unique however I was just messing up thinking it needed to be indexed for me to query based on name haha.

Thanks for the help!

Hi John,

Make sure you are using version 3.0.3 of the plugin. Lookups to other schemas was a new feature added to version 3.0.3… ?? Lucky you! ??

Best regards,
Peter