SQL: Error: near "(": syntax error | primary key problem

Question

The professor asked us to create two tables and alter them.

create table student
(
    name varchar(25) not null,
    std_num int(12),
    std_num(primary)
);

When I click "Run SQL", it shows the following message:

Error: near "(": syntax error

without additional details.

I'm using Programiz online SQL editor.

I tried to delete the following code:

std_num int(12),
std_num(primary)

Then it created the table.

Am I writing a primary key in the table in an incorrect way?

What is the meaning of the message Error: near "(": syntax error?

How do I fix the error?

If my method was wrong, how do I correctly include a primary key in a table?

Answer 1

Since you don't provide the database you are using, I just do a quick guess it should be one of them: MySQL, PostgreSQL, SQL Server, Oracle or SQLite

For all above database's type, the syntax std_num(primary) for defining a primary key, as in your code, is not a standard SQL syntax.

There are two ways to define Primary Key:

1. Inline with Column Definition: The PRIMARY KEY keyword is placed directly in the column definition. This is common when the table has a single-column primary key.

CREATE TABLE student (
    std_num INT PRIMARY KEY,
    name VARCHAR(25) NOT NULL
);

2. Separate Constraint Definition: The PRIMARY KEY keyword is used after all column definitions, especially useful for composite keys (primary keys consisting of multiple columns).

CREATE TABLE student (
    std_num INT,
    name VARCHAR(25) NOT NULL,
    PRIMARY KEY (std_num)
);

Additionally, The int(12) syntax in MySQL does not mean the integer will have 12 digits. Instead, it specifies the display width, which is often a point of confusion. For a primary key, just int is typically sufficient unless you have a specific requirement for the display width.

Answer 2

I am pretty sure that you are using MySQL or MariaDB which usually does not indicates the nature of the error unlike other RDBMSs which provide details. You therefore commit 3 syntax errors:

• The expression must be PRIMARY KEY
• the expression must appear at the head of the definition of a table object (column or constraint)
• and the key words PRIMARY KEY must not appear in parentheses
• and the columns that compound the PRIMARY KEY is a list and must appear in parentheses...

This is a lot of mistakes and I advise you to start by learning the SQL language before starting to write queries....

The right code is :

create table student
(
    name varchar(25) not null,
    std_num int(12),
    PRIMARY KEY (std_num)
);