Solved

Creating a Primary Key after table created

Posted on 2003-11-28
1
3,524 Views
Last Modified: 2008-01-16
I am using PHPMyAdmin to administer my database and I am trying to add a primary key.  The primary key did not come over in the conversion from Access to MySQL for some reason (used Access2MySQL program).  When I attempt to add the primary key from PHPMyAdmin I get the error:

"#1171 - All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL in a key, use UNIQUE instead"

The problem is that there are no rows that have a null value in the column I am trying to declare as the primary key.  When I do a SQL query that reads:

SELECT *
FROM employee
WHERE employee_id is NULL;

I get 0 rows returned.  Anyone have any suggestions?

Thanks.

Jayce

PS.  The version of MySQL I am using is 3.23.53-max-nt ... if that makes any difference
0
Comment
Question by:llcooljayce
1 Comment
 
LVL 3

Accepted Solution

by:
cwp earned 50 total points
ID: 9839602
Using primary keys requires the column to have the "NOT NULL" attribute set (or the "Null" column doesn't have the Yes word in "DESCRIBE employee").

I don't use PHPMyAdmin, so I don't know where, if it is possible, you can set the column attributes, but in SQL code, you'll need to redefine the column:

ALTER TABLE employee CHANGE COLUMN employee_id employee_id INT UNSIGNED NOT NULL;

You should change the "INT UNSIGNED" part to whatever the data type is for the employee_id.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now