Solved

Creating a Primary Key after table created

Posted on 2003-11-28
1
3,534 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

773 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