Viewed   95 times

I'm, generating a SQL query like this in PHP:

$sql = sprintf("UPDATE %s SET %s = %s WHERE %s = %s", ...);

Since almost every part of this query is dynamic I need a way to determine the table's primary key dynamically, so that I'd have a query like this:

$sql = sprintf("UPDATE %s SET %s=%s WHERE PRIMARY_KEY = %s", ...);

Is there a MySQL keyword for a table's primary key, or a way to get it?

I've used the information_schema DB before to find information like this, but it'd be nice if I didn't have to resort to that.


SHOW INDEX FROM <tablename>

You want the row where Key_name = PRIMARY

You'll probably want to cache the results -- it takes a while to run SHOW statements on all the tables you might need to work with.

Monday, December 26, 2022

A key is just a normal index. A way over simplification is to think of it like a card catalog at a library. It points MySQL in the right direction.

A unique key is also used for improved searching speed, but it has the constraint that there can be no duplicated items (there are no two x and y where x is not y and x == y).

The manual explains it as follows:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

A primary key is a 'special' unique key. It basically is a unique key, except that it's used to identify something.

The manual explains how indexes are used in general: here.

In MSSQL, the concepts are similar. There are indexes, unique constraints and primary keys.

Untested, but I believe the MSSQL equivalent is:

  uid varchar(255) NOT NULL CONSTRAINT uid_unique UNIQUE,
  name varchar(255) NOT NULL,
  tag int NOT NULL DEFAULT 0,
  description varchar(255),

CREATE INDEX idx_name ON tmp (name);
CREATE INDEX idx_tag ON tmp (tag);

Edit: the code above is tested to be correct; however, I suspect that there's a much better syntax for doing it. Been a while since I've used SQL server, and apparently I've forgotten quite a bit :).

Thursday, August 18, 2022

Next time, use a single "alter table" statement to update the primary key.

alter table xx drop primary key, add primary key(k1, k2, k3);

To fix things:

create table fixit (user_2, user_1, type, timestamp, n, primary key( user_2, user_1, type) );
lock table fixit write, user_interactions u write, user_interactions write;

insert into fixit 
select user_2, user_1, type, max(timestamp), count(*) n from user_interactions u 
group by user_2, user_1, type
having n > 1;

delete u from user_interactions u, fixit 
where fixit.user_2 = u.user_2 
  and fixit.user_1 = u.user_1 
  and fixit.type = u.type 
  and fixit.timestamp != u.timestamp;

alter table user_interactions add primary key (user_2, user_1, type );

unlock tables;

The lock should stop further updates coming in while your are doing this. How long this takes obviously depends on the size of your table.

The main problem is if you have some duplicates with the same timestamp.

Wednesday, August 10, 2022

It's the same as MySQL ignores the CONSTRAINT pk_PersonID part. You can check by creating the table and then dumping it or issuing SHOW CREATE TABLE Persons.

I guess it supports this syntax only for compatibility with other SQL servers (but ignores it for primary and other local keys) and does not store its information (the constraint name).

However for usage with foreign keys the CONSTRAINT keyword is used also in MySQL.

mysql> CREATE TABLE test.Persons (
    -> P_Id int NOT NULL,
    -> LastName varchar(255) NOT NULL,
    -> FirstName varchar(255),
    -> Address varchar(255),
    -> City varchar(255),
    -> );
Query OK, 0 rows affected (0.50 sec)

server$ mysqldump -p test Persons
Enter password:
-- Table structure for table `Persons`
CREATE TABLE `Persons` (
  `P_Id` int(11) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,

Here is also test to prove MySQL doesn't store the constraint name anywhere and doesn't use it when printing errors (as mentioned for other SQL servers in question What is the purpose of constraint naming :

mysql> insert into Persons (P_Id) values(1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Persons (P_Id) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
Saturday, December 24, 2022

Yes, you can do this (and you should, from a database design point of view).

However, consider what it means if user_id is the primary key on table 2. You are in effect saying that each row in table 2 corresponds to a user, but you already have a table where each row corresponds to a user: table 1. This raises the question "why then don't you put all data of table 2 into nullable columns in table 1?". After all, having two tables means you will have to make two queries to get this data instead of one.

Now there are some scenarios where this practice might be a good idea:

  • if you have lots of users but only a few rows in table 2, perhaps the query on table 2 will be only performed rarely; at the same time, you gain storage space and modification speed on table 1
  • it might be possible in the future for the primary key of table 2 to change, while the foreign key remains; if you put all the data in table 1, this modification would most likely break your database model

It can be a good idea, but it depends on the particulars of your application.

Monday, November 14, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :