LiveCode Forums.

Hi, does anyone know how to find out which is the primary key in a database?

revDatabaseTableNames(databaseID) (excellent, gives you the table names)
revDatabaseColumnNames(recSetID) (as above, for columns)
revDatabaseColumnTypes(recSetID) (thank you rev, tells you data types, crucial 'cause the SQL queries are different for strings & integers).

But to update you need the primary key, since, at least in SQL server and MS access, trying to update the primary key row causes an error (be so much better if these databases simply ignored attempts to update un-updateable columns and continued on to do the rest of the row--but they don't).

So, is there any way for rev/livecode to query a database table and obtain the name of its primary key (if any)?

Seems the SQL for this varies between database programs and i'd like this app to work with any database (and it's essential it works with the various versions of ms access & SQL server).

Presumably this isn't an easy ask or rev would offer a command like revPrimaryKey(databaseID,table_name) but maybe i'm missing something.

Steve

I am still keen on getting primary key info (or any further info relating to column attributes) but now have a useable workaround…
Rather than updating all columns with the one SQL I’ve now done multiple commands (i.e. instead of constructing a single SQL command with column names in a repeat loop and then issuing it, the loop constructs and sends commands for every column). Those that update do and those that can’t be updated don’t.
I then use this feedback to prompt the user to designate un-updateable columns (generally auto-number) which are then saved as prefs for that table (so those columns are skipped in future and their row input fields disabled).
Thanks for bearing with me here; hopefully it could help someone else with a similar problem.
Steve

Steve-

I'm not sure what exactly you're trying to do here, but wouldn't it be better to start from understanding the database schema *before* updating records?

Unfortunately, AFAIK there's no cross-database-platform way to determine primary key fields, and certainly not from just SQL queries. If you have admin access to the SQLServer databases you might look into the SysTables and see what you can pull out. But I've seen way too many databases that have been designed with no primary keys, no links between tables, and/or real data used as primary and foreign keys instead of designating a non-data id field as a primary key. So with a well-designed database you might have a chance at this, but in the real world it's probably best not to get your hopes up too high.

Yes, my hopes weren't all that high, in fact, i was told to ask the above question by the powers that be (true story).

Best i've been able to manage is to find the column(s) with the most unique values, pick the first of those and assign a local 'primary key' for my app to use on that basis. I'm thinking that will work a good deal (if not most) of the time

If the user knows the real primary key and it's different they can change it (they're prompted to, along with a little spiel on what a primary key is).

If their database is shite i can't do much about it. If there's no column with all unique values then clicking on a row might well display some other row's data.

If they try to manually update an un-updateable column they get a message telling them why it hasn't worked (and which columns) and a prompt to save that info so the app won't let it happen again.

Be much better if database vendor's standardised their products so this could all be found out beforehand.

I agree (BTW) i don't think it's too much to expect a user to know something about their database & set up my app accordingly before using (powers that be disagree).

Steve