Cannot add primary key constraint

Question

I'm trying to create a table and add a constraint with alter table but it's not working:

CREATE TABLE customer
(
cust_id NUMBER(3),
cust_name VARCHAR2(3),
phone_no NUMBER(10)
);

ALTER  TABLE  customer
ADD  CONSTRAINT  pk_customer
PRIMARY  KEY  (cust_id);

This is the error:

near "CONSTRAINT": syntax error

What did I do wrong?

https://stackoverflow.com/questions/8859353/alter-table-to-add-a-composite-primary-key or https://stackoverflow.com/questions/8761570/how-can-i-alter-a-primary-key-constraint-using-sql-syntax — Sebastian Brosch, Jun 29 '19 at 17:35
I think you need to delete the text ' CONSTRAINT pk_customer'. If you are new with MySQL, probably you find [this](http://www.mysqltutorial.org/mysql-primary-key/) useful. — Antonio Veneroso Contreras, Jun 29 '19 at 17:36
You need to show us the full error message. Also I think you made a mistake in the tags. It should be [tag:mysql] instead of [tag:mysqli] — Dharman, Jun 29 '19 at 18:07
@TheImpaler Why would you say it is Oracle? It was a mistake in the tags... see my comment above. — Dharman, Jun 29 '19 at 18:11
@Dharman Because of `VARCHAR2`... and because it works fine in Oracle 12c. — The Impaler, Jun 29 '19 at 18:12
Also `NUMBER` is an Oracle data type, not recognized by MySQL. — Bill Karwin, Jun 29 '19 at 18:44

Traian GEICU · Answer 1 · 2019-06-29T18:34:55.490

0

for mysql try with

ALTER TABLE customer
ADD PRIMARY KEY(cust_id);

or directly from CREATE TABLE

Attention! : compatible fields types within mysql

CREATE TABLE customer
(
    cust_id NUMBER(3),
    cust_name VARCHAR2(3),
    phone_no NUMBER(10),
    PRIMARY KEY(cust_id)
);

edited Jun 29 '19 at 18:34

answered Jun 29 '19 at 17:44

Traian GEICU

1,750
3
14
26

Your answer also contains `VARCHAR2` from Oracle DB. You might want to take this into consideration. – Dharman Jun 29 '19 at 18:14
@Dharman, yes just copy his field as were designed. The main question was about PK and assumed that first query with `CREATE TABLE` was working before issue `ALTER`.(assuming just a typo). If not then is another story ... – Traian GEICU Jun 29 '19 at 18:19
@Dharman also even `NUMBER(10)` is not a valid type within `mysql` but here was for him to check. – Traian GEICU Jun 29 '19 at 18:31

The Impaler · Answer 2 · 2019-06-30T15:43:28.527

0

Your statements work well... in Oracle database (12c1 at least).

In order for your query to work in MySQL, you need to change the data types. (see SQL Fiddle). For example:

CREATE TABLE customer
(
cust_id DECIMAL(3), -- changed here
cust_name VARCHAR(3), -- changed here
phone_no DECIMAL(10) -- changed here
);

ALTER  TABLE  customer
ADD  CONSTRAINT  pk_customer
PRIMARY  KEY  (cust_id);

edited Jun 30 '19 at 15:43

answered Jun 29 '19 at 18:12

The Impaler

45,731
9
39
76

Sorry for reply late, I use https://jsfiddle.net/(mysql online) for the code above and get the error – Đức Huỳnh Jun 30 '19 at 15:35
That would be correct, since `VARCHAR2` and `NUMBER` are **not valid** column types in MySQL. – The Impaler Jun 30 '19 at 15:37
I was testing by replace all, just keep cust_id with INT, like this: `CREATE TABLE customer ( cust_id INT ); ALTER TABLE customer ADD CONSTRAINT pk_customer PRIMARY KEY (cust_id);` but it still get the same error – Đức Huỳnh Jun 30 '19 at 15:43
Thanks, It works, but in this website (https://www.jdoodle.com/execute-sql-online), your code get the error, can u explain why, I don't get it – Đức Huỳnh Jun 30 '19 at 15:48
OK, that site is for SQLite only, not MySQL. Totally different database engine. – The Impaler Jun 30 '19 at 15:50

Cannot add primary key constraint

2 Answers2