SQL query WHERE primary_key = x

Question

SQL query WHERE primary_key = x

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I was looking for a way to get a tuple where your primary key is equal to the value that I'm passing by parameter, that is:

SELECT * FROM clientes WHERE primary_key = 1;

Or it could be:

SELECT * FROM boletas WHERE primary_key = 'boleta1';

The thing is that I do not know in advance which is the primary key, nor its type, but I know that the parameter to evaluate is going to be correct .

I would also need an SQL statement since the ones I found are from MySQL (eg SHOW KEYS FROM clientes WHERE Key_name = 'PRIMARY' ) or SQL Server, what I need is something typical of native SQL, which any DBMS can understand, since the system could have to face anyone.

Is it possible to do this?

sql

asked by Genarito 01.06.2017 в 15:14

source

2 answers

0

I do not know if I understand your question well, but basically I know 2 SQL dialects that are: PL SQL (typical of Oracle) and TRANSACT SQL which is the variant used by SQL Server or MySQL among others. In turn, each implementation has variants in terms that are not of the common corpus of the language. Anyway, the query that you want to make should be able to be interpreted by any of the variants. The only problem is in the data type because as far as I know, all the variants will try to differentiate between one of the types string and one of the types number .

Then, if the parameter you pass to it must be a number, it should not be enclosed in the SQL statement. If it is a chain, yes. That is, in the case that the ID is number 1, but the type is string:

SELECT * FROM boletas WHERE primary_key = '1';

For the case where the ID is 1 and the type is numeric:

SELECT * FROM boletas WHERE primary_key = 1;

answered by 01.06.2017 в 15:50

Passing onClick parameters of btn Android Show a div only when you enter the page the first time

score 2 · Accepted Answer

I do not think there is an "ANSI" way to get to that information, each engine implements different ways to access said data, in a time SQL Server for example, offered and recommended the use of some Sp's sp_help_* , with time these data were standardized in tables or views of the engine so that retrieving this data can be done with a simple SQL query, ie you have variants between engines and also between versions of the same engine. As you already told @CharlieBits, you would need an abstraction layer ie a ORM to be able to standardize your code. Anyway, by way of example, if it helps, I'll give you an example in SQL 2012:

SELECT  
    o.name, 
    i.name,
    i.is_primary_key, 
    c.name,
    t.name
    FROM sys.indexes i
    INNER JOIN sys.index_columns ic
        ON ic.object_id = i.object_id
        AND ic.index_id = i.index_id
    INNER JOIN sys.columns c
        ON c.object_id = i.object_id
        AND c.column_id = ic.column_id
    INNER JOIN sys.types t
        on t.system_type_id = c.system_type_id
    INNER JOIN sys.objects o
        ON o.object_id = i.object_id
    ORDER BY 1

Adding the appropriate WHERE you can know from a table which is the primary key (if it has one) the one or columns that make up that index and the data type of each of them.