2. PHPRunner General questions
  3. [SOLVED]  1 Primary Key - Another not unique
This topic is locked
[SOLVED]

 1 Primary Key - Another not unique

 		9/22/2018 7:46:52 AM
PHPRunner General questions
lefty author

I have a customer file that I import to project each month . I am now using 9.8 and in the future 10.0 which should check for existing records and either update or add new records according to the manual if using a primary key in file. My older project was 9.7 I just truncated table and uploaded a new Customer file, Which I do not want to do anymore due to google map fields which I have to do geocoding every time I import a new file. So I made CustomerID the primary key instead of auto_number . Fine. Only problem is there are several CustomerID's that have EmployeesID's connected to the same Customer ( hence 3 records of CustomerID ) so it can't be the primary key. So I want to use a second key EmployeeID / or non unique field in the table mysql to import . My table looks something like this
CustomerID

EmployeeID

Employee

Division

fldgroup

address

city

state

zip

lat

lng

map
Is it possible to have a primary key and a non-unique composite key in mysql table / project - upon the import . I tried this link with constraint with multiple fields, but I keep getting syntax error in query wizard in my project. If I could use two fields it would make the record unique.
Or do I have to import a file like this

CustomerID

EmployeeID1

EmployeeID2

EmployeeID3
which I am trying to avoid due to numerous Session calls of just the one field calledEmployeeID (connections and events) in other tables in the project.
Thanks for any ideas in advance.
H
 		headingwest 9/22/2018

Hi John,
Whats the error (getting syntax error in query wizard)
If you download and use HeidiSQL it might be easier to see your keys. You'd have to make sure that you delete the original CustomerID key if that's blocking import because it's no longer unique.
Sometimes I just throw in an extra key column if that's what's needed.
Eg:

recordid (unique, autoincrement)

customerid

Employeeid
This allows multiple employee/customer records. You can add a unique constraint on customer/employee - unless say your doing "hoursworked" in which case there might be multiple
Jim.
lefty author 9/22/2018



Hi John,
Whats the error (getting syntax error in query wizard)
If you download and use HeidiSQL it might be easier to see your keys. You'd have to make sure that you delete the original CustomerID key if that's blocking import because it's no longer unique.
Sometimes I just throw in an extra key column if that's what's needed.
Eg:

recordid (unique, autoincrement)

customerid

Employeeid
This allows multiple employee/customer records. You can add a unique constraint on customer/employee - unless say your doing "hoursworked" in which case there might be multiple
Jim.


Thanks for the reply
I see the what your saying . I did not try another field to use as the primary key and combine. So I should try
[font="Consolas,"]
[font="Consolas,"]
only issue is that I don't think phprunner import will not recognize recordid because it is not in the import file . So it will just add all records in the import function as if it was just another auto_number field. Will have to do more testing. Thanks