1

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.

2

You might need INFORMATION_SCHEMA.KEY_COLUMN_USAGE

You would have to issue this query

SELECT column_name
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will retrieve every column in the PRIMARY KEY.

You could then make this query generate the ALTER TABLE clauses for you as follows:

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will produce a comma-separated list of MODIFY COLUMN clauses you need to apply to the column after dropping the PRIMARY KEY. If you want to drop any of these columns, you have to change MODIFY COLUMN to DROP COLUMN for the columns that need to be dropped.

Give it a Try !!!

UPDATE 2014-12-09 14:00 EST

Your comment

I'm trying to understand how this works. GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL')) is especially confusing to me. Where does column_name come from in this query? I tried running it as is in MySQL, but I get no results

To give you a general idea of how query can help you, here is a table in MySQL on my laptop

mysql> show create table weird.vinner\G
*************************** 1. row ***************************
       Table: vinner
Create Table: CREATE TABLE `vinner` (
  `startnr` int(11) NOT NULL,
  `alder` varchar(25) NOT NULL,
  `kjonn` varchar(25) NOT NULL,
  `ovelseid` varchar(25) NOT NULL,
  `slutttid` int(11) NOT NULL,
  PRIMARY KEY (`startnr`,`slutttid`),
  KEY `slutttid` (`slutttid`),
  CONSTRAINT `vinner_ibfk_1` FOREIGN KEY (`slutttid`) REFERENCES `passering` (`slutttid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

See the PRIMARY KEY. It has two columns: startnr and slutttid.

Now, if I run my code on that tabl, I get this list

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
    -> FROM information_schema.key_column_usage
    -> WHERE table_schema='weird'
    -> AND table_name='vinner'
    -> AND constraint_name='PRIMARY';
+------------------------------------------------------------------------+
| GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))     |
+------------------------------------------------------------------------+
| MODIFY COLUMN startnr DEFAULT NULL,MODIFY COLUMN slutttid DEFAULT NULL |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

The output creates a MODIFY COLUMN clause for both columns.

If you were dropping the slutttid column, you would use PHP to change MODIFY to DROP

MODIFY COLUMN startnr DEFAULT NULL,DROP COLUMN slutttid

You would be doing this kind of code to generate the clauses needed

| improve this answer | |
  • I'm trying to understand how this works. GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL')) is especially confusing to me. Where does column_name come from in this query? I tried running it as is in MySQL, but I get no results. – Gn13l Dec 9 '14 at 18:48
  • Updated my answer – RolandoMySQLDBA Dec 9 '14 at 19:00
  • Ok, I think I get it now. GROUP_CONCAT iterates over the results of the select query and puts it into a single string. It was rather absent minded of me to run your code without replacing the variables with relevant terms. Still though, this seems very similar to programming it in PHP. Is there not a way to say set the properties in the information schema table? – Gn13l Dec 9 '14 at 19:17
  • No, you cannot. INFORMATION_SCHEMA is a database made up of several read-only temporary tables using the MEMORY storage engine. You cannot tweak to change tables because there are no triggers that make it go back and update the physical files of tables. For your question, the basic idea is to have INFORMATION_SCHEMA create the SQL for you. Please see my post dba.stackexchange.com/questions/3335/… on how I do this to collect diskspace statistics. – RolandoMySQLDBA Dec 9 '14 at 19:37
  • Thank you for the information, it's good to know. Is it safe to say then that there's not a way to accomplish my question without using some level programming, even if it is via MySQL? – Gn13l Dec 9 '14 at 20:28
-1

I wrote this script to output the scripts to change all columns in a database to allow nulls:

select 
case
when sc.max_length = '-1' and st.name in ('char','decimal','nvarchar','varchar')
then
'alter table  [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(MAX) NULL'
when st.name in ('char','decimal','nvarchar','varchar')
then
'alter table  [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(' + cast(sc.max_length as varchar(4)) + ') NULL'
else
'alter table  [' + so.name + '] alter column [' + sc.name + '] ' + st.name + ' NULL'
end as query
from sys.columns sc
inner join sys.types st on st.system_type_id = sc.system_type_id
inner join sys.objects so on so.object_id = sc.object_id
where so.type = 'U'
and st.name <> 'timestamp'
order by st.name
| improve this answer | |
  • 2
    That is invalid for MySQL – a_horse_with_no_name Mar 13 '19 at 7:03
  • This "appears" to be good for SQL Server (I say appears because it's not my area), but answers with code should, at least, have been tested on SQLFiddles or a small test db in-house. Check out the Questions section here (disclaimer - my profile) - it also references answers. p.s. the script looks technically proficient - I hope you contribute more in the future :-) – Vérace Mar 13 '19 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.