Solved

Creating a Primary Key after table created

Posted on 2003-11-28
1
3,542 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql update statement 3 40
MS Access - need to reduce row size 25 78
Concat multiple records into one line 3 72
Delete image(s) associated with record(s) 16 46
Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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