Changing a NOT NULL table to NULL

[eMonk]

I tried the following alter query but phpmyadmin still shows "No" under the Null column.

 

ALTER table model_in_city Modify city_id int unsigned null;

 

Also tried changing it in phpmyadmin and picked "null" from the dropdown menu but it's still displaying as not null. Any ideas?

 

[gizmola]

run this in the sql window:

 

describe model_in_city 

 

Paste the results in a reply.

[eMonk]

Field	Type	Null	Key	Default	Extra
model_id	int(10) unsigned	NO	PRI	 	 
city_id	int(10) unsigned	NO	PRI	0	 
city_display	tinyint(2) unsigned	YES	 	NULL	 

[gizmola]

city_id is part of the primary key of the table, so it can not be null.

[eMonk]

Oh, I see thanks gizmola.

[eMonk]

How can I remove the primary key for city_id?

 

I tried to remove it in phpmyadmin but got this error:

 

Error

SQL query:

 

ALTER TABLE  `model_in_city` DROP PRIMARY KEY ,

ADD PRIMARY KEY (  `model_id` )

 

MySQL said:

 

#1062 - Duplicate entry '17' for key 1

[gizmola]

That error is telling you you have more than one row with '17' in the model_id column.  So mysql will not let you declare that to be the pk because pk's must uniquely identify a row. 

[eMonk]

model_id is the only unique column in the table. I had to use multiple inserts with the same model_id. So it's no longer unique? Should I remove the primary key from this as well and have no primary keys in this table?

[gizmola]

if the table can have more than one row with the same model_id, then model_id can not be the primary key by itself. The primary key, whether it be one column or more, has to be able to be used to uniquely identify any one row in the table.

[eMonk]

I don't think I have any primary keys in this table then. Would it be ok to not have a primary key in this table?

[eMonk]

Actually, I think model_id and city_display can be the primary key...

[eMonk]

Also, what happens when you use primary keys incorrectly like I just did.

 

This table appears to be working without any known problems. I've been inserting, selecting, deleting, etc from this table fine...

[gizmola]

Technically you do not need to have a primary key, but not having one in most cases is a bad idea.  When you define a PK, you are also creating a unique index for the pk, that will be used in queries and joins.

[eMonk]

I made model_id and city_display as the primary key without errors and it can be unique in one row in the table.

 

Thanks for the wealth of info gizmola! It makes sense now.