Invalid use of null value

Question

I have a problem using mySQL.

This error pops up:

Invalid use of null value

I was trying to make two attributes in a table the primary key; here is my code:

alter table contact_info
add primary key(phone_number, contactID);

Here are the alter statements I put into my contact_info table:

alter table contact_info
add contactID varchar(10);

alter table contact_info
add suffixID varchar(8);

alter table contact_info
add titleID varchar(12);

alter table contact_info
add companyID varchar(12);

alter table contact_info
add phonetypeID char(2);

Does anybody know what's wrong? Thanks in advance.

Answer 1

Look for a contact_info that has a null value in phone_number or contactID. You can't add a primary key with existing null values in the table.

select *
from contact_info
where (phone_number is null or contactID is null)

Run that SQL to find any records where the values are null. Update the records, then try applying your primary key again.

I'm not sure what you are doing, but you may want to back up your data first!!! before running any updates. Here is an update you might be able to use to set a contactID:

update contact_info
set contactID = (select max(contactID) from contact_info) + 1
where contactID is null

update contact_info
set phone_number = '555-1212'
where phone_number is null

If you have duplicates in your data, you need to find them and update those as well. Here's how you can find duplicates:

-- Assuming the phone_number is duplicate (2 people living in the same house with the same phone number)
select a.phone_number, count(1)
from contact_info a
group by a.phone_number
having count(1) > 1