Foreign Key - GUID - Multiple table update - MsSQL

benracer

I would really appreicate some help with some SQL queries...

I have 3 tables...

People

GUID Name

1 Ben
2 Dave
3 Bob
4 Grace

Cars

People_GUID Car

1 Audi
1 BMW
3 Ford
4 Ford

Bikes

People_GUID Car

1 BMW
1 Aprilia
3 Honda

Now, I need to change the People.GUID and update in children tables (Bikes.People_GUID, Cars.People_GUID).

Something like this?

UPDATE
Bikes, Cars
SET (People.GUID, Bikes.People_GUID, Cars.People_GUID)=newID()
WHERE (SELECT * FROM People)

NewID should generate a random microsoft GUID?

Thanks!

laserlight

In this case, there are two approaches that I would choose from:

Have the foreign keys set to ON UPDATE CASCADE. This way, you only need to change the GUID in the People table and the change would be automatically cascaded to the other two tables.
Use an auto-incremented integer primary key intended solely for that purpose. This way, the GUID is only stored in the People table; the other two tables would have a foreign key that never changes.

johanafm

Moreover, you should normalize your table structure to contain tables People (id, name, MS_GUID), Cars(id, name), Bikes(id, name), People_Cars(people_id, car_id) and People_Bikes(people_id, bike_id). As it is, you have redundant data in cars with multiple entries for Ford.

I'd definitely go with option 2 above, i.e. add GUID to people table and leave all ids for people as they are. Primary keys should not be changed if they provide no other data than some arbitrary data to uniquely identify the row. If you are using a primary key which represents some existing part of the data described by the relation, it can of course change and in that case an on update cascade is the way to go.

If by Micorsoft GUID you mean a string such as 6B29FC40-CA47-1067-B31D-00DD010662DA, you are also better off using some kind of unsigned integer for your primary key. The above has to be represented as 36 characters, which in turn means a long string index and also wasted storage space for all tables where it appears as foreign key. A primary key of INT UNSIGNED will probably take 4 bytes (check manual for whatever DBMS you use) - enough to store 4.29 billion people.