Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating a Primary Key after table created

Posted on 2003-11-28
1
Medium Priority
?
3,563 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 200 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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