Question 20814 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Unique Key and not primary key

I’m learning from an online course MySQL using the WampServer and editing code in MySQL Workbench.

Trying to create a table with the following code, the column nome appears to be the primary key from the table, but i’m only using the unique constraint.
When i don’t use the unique constraint, the code runs normally and don’t give me a primary key.

create table if not exists cursos (
nome varchar(30) not null unique,
descricao text,
carga int unsigned,
totalaulas int unsigned,
ano year default '2016'
) default charset utf8mb4;

Second to this question, when i was trying to drop the constraint primary key i was getting the error Error Code: 1091. Can’t DROP ‘PRIMARY’;

alter table cursos
drop primary key;

So, in resume, i’m trying to use the unique constraint without setting a column has a primary key, and then i’m trying to drop the primary key constraint.

Edit:

When i call describe table i get this, the column nome is defined has primary key whithout i using the constraint.

+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| idcurso          | int         | YES  |     | NULL    |                |
| nome             | varchar(30) | NO   | PRI | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+

What i’m trying to do is drop the primary key from nome and put on idcurso, but when i use the code

alter table cursos
add primary key idcurso;

I get the error Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line

Tags:

2

Answers


  1. 0

    If you create yet another column, perhaps guid,
    that has a UNIQUE constraint,
    then you will be able to do what you wish.
    (Not sure why you wish it. Whatever.)

    A table can have multiple UNIQUE constraints.

    Each table should have a PRIMARY KEY (which will of course be UNIQUE).
    It affects physical layout of the rows on disk,
    which affects retrieval speed and how the query planner behaves.
    If you do not use the PRIMARY keyword, then the first UNIQUE column
    will effectively be the primary key.

    Put another way, to ALTER the table as you wish,
    the backend DB will need to be able to promote
    some candidate column so it becomes the new PRIMARY KEY.

    Login or Signup to reply.
  2. 0

    I tried this on mysql command-line client connected to a MySQL Server 5.5.2. Here is what happens:

    mysql> create table if not exists cursos (
    -> nome varchar(30) not null unique,
    -> descricao text,
    -> carga int unsigned,
    -> totalaulas int unsigned,
    -> ano year default '2016'
    -> ) default charset utf8mb4;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> desc cursos;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| nome       | varchar(30)      | NO   | PRI | NULL    |       |
| descricao  | text             | YES  |     | NULL    |       |
| carga      | int(10) unsigned | YES  |     | NULL    |       |
| totalaulas | int(10) unsigned | YES  |     | NULL    |       |
| ano        | year(4)          | YES  |     | 2016    |       |
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.05 sec)

    Note that the field nome has a Key as PRI. But, it is only a description and not a fact. If you try to DROP the primary key you will see an error like below:

    mysql> alter table cursos drop primary key;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists

    Add a new column and make it a primary key. Note the DESCRIPTION after that.

    mysql> alter table cursos add idcurso int primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc cursos;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| nome       | varchar(30)      | NO   | UNI | NULL    |       |
| descricao  | text             | YES  |     | NULL    |       |
| carga      | int(10) unsigned | YES  |     | NULL    |       |
| totalaulas | int(10) unsigned | YES  |     | NULL    |       |
| ano        | year(4)          | YES  |     | 2016    |       |
| idcurso    | int(11)          | NO   | PRI | NULL    |       |
+------------+------------------+------+-----+---------+-------+
6 rows in set (0.05 sec)

    At this point, you can drop the primary key using the syntax: alter table cursos drop primary key;. This will drop the primary key constraint only (but, not the column definition).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search