ColdFusioning: Using ColdFusion and Oracle To Get A Table's Primary Key

ColdFusioning: Pronunciation \kold-fy�-zhn-ing\ Noun: The action of one that writes ColdFusion.

Using ColdFusion and Oracle To Get A Table's Primary Key

Related Categories: Database, ColdFusion

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>

Then you only need to call the function to get the primary key name: