CODEWITHSUNDEEP
mysqlsqlmysql-error-1138
Daniel Ashfall Zhou
Daniel Ashfall Zhou

Reputation: 167

Invalid use of null value

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.

Upvotes: 10

Views: 20109

Answers (2)

Jess
Jess

Reputation: 25079

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

Upvotes: 11

Matt Busche
Matt Busche

Reputation: 14333

Running this query will tell you where the offending columns are. You cannot set a NULL value to be the primary key.

SELECT *
FROM contact_info
WHERE phone_number IS NULL
OR contactID IS NULL

Upvotes: 1

Related Questions