Using ColdFusion and Oracle To Get A Table's Primary Key
I recently ran into a situation where I needed to get the primary key of a table, knowing only the table name. Using the following code, you can pass it a table name, and it will return the primary key:
<cfcomponent>
<cffunction name="getPrimaryKeyName" output="false">
<cfargument name="Table_Name">
<cfquery name="getPrimaryKeyName" datasource="#request.maindsn#">
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = <cfqueryparam cfsqltype="cf_sql_text" value="#UCase(arguments.Table_Name)#">
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position
</cfquery>
<cfreturn getPrimaryKeyName.column_name>
</cffunction>
</cfcomponent>
<cffunction name="getPrimaryKeyName" output="false">
<cfargument name="Table_Name">
<cfquery name="getPrimaryKeyName" datasource="#request.maindsn#">
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = <cfqueryparam cfsqltype="cf_sql_text" value="#UCase(arguments.Table_Name)#">
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position
</cfquery>
<cfreturn getPrimaryKeyName.column_name>
</cffunction>
</cfcomponent>
Then you only need to call the function to get the primary key name:
<cfinvoke method = "getPrimaryKeyName" returnVariable = "request.PrimaryKeyName">
<cfinvokeargument name="Table_Name" value="#arguments.Table_Name#">
</cfinvoke>
<cfinvokeargument name="Table_Name" value="#arguments.Table_Name#">
</cfinvoke>
