I'm working on a PHP script to modify the primary key (PK) of a table. My current code is:
$q = "SHOW KEYS FROM $db.$table WHERE Key_name='PRIMARY';";
$keys = Query ( $q);
if ( $keys)
{
$q = "ALTER TABLE $db.$table
DROP PRIMARY KEY,
ADD PRIMARY KEY ($keyQ);";
$res = Query ( $q);
}
else
{
$q = "ALTER TABLE $db.$table
ADD PRIMARY KEY ($keyQ);";
$res = Query ( $q);
}
This mostly works for what I need. The only problem is that if I drop a field as a PK, it retains the NULL='No' and DEFAULT=0 properties from being a PK.
Since I'm going to define the PK later and I generally don't need not null and default constraints I would like to clear all not null values and default values. Then I can proceed to define the primary key and know that the primary key is the only columns with default values and not NULL constraints.
Is there a way to do this with MySQL? I could do it with PHP, but it would probably be more work and would be less elegant.
Best Answer
You might need INFORMATION_SCHEMA.KEY_COLUMN_USAGE
You would have to issue this query
This will retrieve every column in the
PRIMARY KEY
.You could then make this query generate the
ALTER TABLE
clauses for you as follows:This will produce a comma-separated list of
MODIFY COLUMN
clauses you need to apply to the column after dropping thePRIMARY KEY
. If you want to drop any of these columns, you have to changeMODIFY COLUMN
toDROP COLUMN
for the columns that need to be dropped.Give it a Try !!!
UPDATE 2014-12-09 14:00 EST
Your comment
To give you a general idea of how query can help you, here is a table in MySQL on my laptop
See the PRIMARY KEY. It has two columns:
startnr
andslutttid
.Now, if I run my code on that tabl, I get this list
The output creates a MODIFY COLUMN clause for both columns.
If you were dropping the slutttid column, you would use PHP to change
MODIFY
toDROP
You would be doing this kind of code to generate the clauses needed