[HELP] Enforcing Referential Integrity

[Solar]

Hey PHPFreaks!

I've ran into a problem and hoping you could help me out. I did not design these tables and I am having problems enforcing referential integrity.
Is a constraint required for this task?

Here are the two tables

ClientsTable (ClientID, ClientName)

VALUES("11111TOR","JOE");
VALUES("22222OTT","BOB");
VALUES("33333NEW","PAM");

 

TimezoneTable (Location, Timezone)
VALUES("OTT", "EST")
VALUES("TOR", "EST")
VALUES("CAL", "MDI")
VALUES("NEW","EST")

There is a one to many relationship between TimezoneTable and ClientsTable. Due to the data, I cannot enforce referential integrity.

What is a solution in joining these together? Could I do so by creating a new table? How could I go from there?

Thanks in advanced,
Solar

[Barand]

Is the clientid unique without the location tacked on the end?

 

In other words is there, or can there be, a 11111TOR and a 11111NEW for example?

Edited April 17, 2013 by Barand

[Solar]

I don't see why not. The instructions given to me did not inform me that ClientID was unique or not. Would there be a huge difference if it was unique?

If the concept is stripping the numbers off of ClientID and referencing it that way... is that possible?

[Solar]

Apparently since I cannot enforce referential integrity, I must deal with it using an INNER JOIN statement if I want to run SELECT statements.

INNER JOIN TimeZonesTableON Location=SUBSTRING(ClientID,6,3)

Thanks Barand for your help.

[Barand]

Sorry I didn't get back earlier - sleep time

 

You could split the id field into 2 fields. If the numeric bit is not unique on its own (would be better if it were) then you would define a compound primary key using both fields. You could then define referential integrity for the location part.

 

if it the combination of location/id that is unique then you could define the the table like this, ensuring clientid is unique within a location

location CHAR(3) NOT NULL,
clientid INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(location, clientid),
KEY `loc` (`location`),
CONSTRAINT `loc` FOREIGN KEY (`location`) REFERENCES `timezone` (`location`) 

Edited April 18, 2013 by Barand