1

I'm trying to code a general 'update' function for all my tables. However, the name of the primary keys is different for each of them. So is it possible, instead of using the name of the primary key column, to use something like which would automatically select the primary key column ?

UPDATE .... WHERE PRIMARY KEY = 'id_number'

Thanks!

Edit: Sorry, it seems I'm not clear, but I must admit I'm really not an expert. So the idea is that I would like it to work on tables that all have only one INT type primary key column. And I would like if possible to avoid making two queries (one for getting the name of the primary key column + the query itself). At first, I wanted to avoid having to make sure I have the right name of the column for each table (mo_id, rc_id etc...).

benRollag
  • 1,219
  • 4
  • 16
  • 21
cam
  • 23
  • 4
  • 2
    You don't state RDBMS but I doubt it in any. A primary key can be composite (multiple columns) for starters. – Martin Smith Jul 11 '12 at 15:35
  • Which DBMS? This info matters. Try and be as clear as possible. – GrayFox374 Jul 11 '12 at 15:37
  • I don't think it can be done with one query. might need to use the information schema to achieve this. Loop through all table names, find the PK and generate the update query for each table – codingbiz Jul 11 '12 at 15:40
  • 1
    Your tables might all have an INTEGER primary key, but will your tables all have the same columns? If not, you will have a different SET... clause for each table. Usually each table's update proc is specific to that table. – Tim Jul 11 '12 at 16:02
  • You say that you `want to avoid having to make sure [you] have the right name of the [primary key] column for each table`. It is good practice to develop naming conventions, like CUSTID, i.e. {tablename}id, for the primary key, so that you don't have to keep consulting the table definition when writing queries that use joins. But one of the things about SQL -- you have to check your column names. Some kinds of laziness are good in that they encourage efficiency; but other kinds of laziness are, well, just laziness. :-) – Tim Jul 11 '12 at 16:07
  • @Tim you may be right, maybe I should just rename all the id fields to make it simple. But I would have thought it would be possible to do the request I described (although I'm clearly not the expert). – cam Jul 11 '12 at 16:26
  • @cam: although it might be possible to retrieve the name(s) of the column(s) that comprise the primary key, there is also a downside: your SQL would have to be a dynamically created string and so you'd not get any of the precompilation performance benefits. You're going down the wrong path in trying to do it this way, for any number of reasons. – Tim Jul 11 '12 at 18:12

1 Answers1

1

I'm going to say that you cannot make a general update function for all your tables. Actually it would need to be a stored procedure, the way I'm thinking of it, but that's another matter. As @Martin Smith points out, the PK can be a composite. I've had PKs that have had four fields tied to make it unique. How can you account for that? Also, the PK can be a number of different types. Are you going to code for all of them in this general purpose sproc? Your snippet assumers the PK is a char type. This is not looking plausible. I did find some code to let you ID the PK of any table, but you have a lot more work to do after that, and your full requirements aren't clear.

From the vaults (this works for SQL Server):

 SELECT KU.table_name as tablename,column_name as primarykeycolumn
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
 INNER JOIN
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
 ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
 TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
 and ku.table_name='yourTableName'
 ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

from SQL Server: Get table primary key using sql query

Community
  • 1
  • 1
GrayFox374
  • 1,742
  • 9
  • 13
  • Yep, you can't guarantee the PK is an int or even a single column - I have a piece of software which interfaces with Oracle's SAP platform and the PK consists of 6 fields! – Charleh Jul 12 '12 at 16:22