Help

[Pir]

I have a created a table 'data' with two columns: id, name.
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(50) | | PRI | | |
+-------+---------------+------+-----+---------+----------------+

As you can see above, id is set as 'auto_increment ' so MySQL requires
it to be primary key. I want to avoid same name again so I made the
second column 'name' to be primary key as well.

When I insert a name in the table, say 'John', I can do that with the
SQL statement - INSERT INTO data values ('',"John");. If I want to
insert the same name again, MySQL lets me do that so now I have two
entries for 'John' (that I do not want). Can you help me how I can
avoid this.

Pir.

[Felix Geerinckx]

On 19/04/2005, Pir wrote:

I have a created a table 'data' with two columns: id, name.
+-------+---------------+------+-----+---------+----------------+

Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+----------------+

id | int(11) | | PRI | NULL | auto_increment |
name | varchar(50) | | PRI | | |

+-------+---------------+------+-----+---------+----------------+

As you can see above, id is set as 'auto_increment ' so MySQL requires
it to be primary key. I want to avoid same name again so I made the
second column 'name' to be primary key as well.

When I insert a name in the table, say 'John', I can do that with the
SQL statement - INSERT INTO data values ('',"John");. If I want to
insert the same name again, MySQL lets me do that so now I have two
entries for 'John' (that I do not want). Can you help me how I can
avoid this.

That's because the combination of id and name have to be unique which
is always the case since id is an autonumber.

Use:
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
);

Also, don't INSERT INTO foo VALUES ('', "John");
but INSERT INTO foo VALUES (NULL, "John");
--
felix

[Pir]

Thanks. I really appreciate your help. As you mentioned in your post
that I set 'id' to be the primary key and set 'name' to unique. Is it
possible to change the column 'name' which is presently a primary key
to 'unique' without re-creating the table?

The reason is: I already have a lot of data in the table. Is is
possible to avoid re-entering the hundreds of records?

Thanks once again.
Pir

[Felix Geerinckx]

On 20/04/2005, Pir wrote:

Is it
possible to change the column 'name' which is presently a primary key
to 'unique' without re-creating the table?

Try the following code:

USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT,
name CHAR(32) NOT NULL,
PRIMARY KEY (id, name )
) Engine = MyISAM; -- oops ... wrong table definition

INSERT INTO foo VALUES(NULL, 'John');

-- auto_increment should always be a key.
-- In order to be able to drop the primary key, we create a
-- temporary key
ALTER TABLE foo ADD UNIQUE temp (id);

-- now we can drop the primary key (with the name part) ...
ALTER TABLE foo DROP PRIMARY KEY;

-- ... and recreate the keys like we really want
ALTER TABLE foo ADD PRIMARY KEY (id);
ALTER TABLE foo ADD UNIQUE name (name);

-- we no longer need the temporary key
ALTER TABLE foo DROP INDEX temp;

-- Let's see if it worked: this should fail
INSERT INTO foo VALUES(NULL, 'John');

-- What is the index status?
SHOW INDEX FROM foo;

Beware: before running table modifying code it's always a good idea to
backup your table(s) first with e.g. the mysqldump program.

An alternative approach is to
(1) dump your table with mysqldump,
(2) edit the CREATE TABLE statement in the dump-file
(3) reload the dump file in your database

--
felix