CODEWITHSUNDEEP
mariadbconstraintsnotnull
Huolsam
Huolsam

Reputation: 1

How to set a name for not null constraint in MariaDB

i tried to set names for constraints in MariaDB like i used to do in oracle but i got error.

This my code:

"CREATE TABLE TBLPROFILES(
ID_PROFILE INT(3) UNSIGNED AUTO_INCREMENT,
PROFILE_NAME VARCHAR(10) CONSTRAINT NN_PROFILES_PROFILE_NAME NOT NULL,
CONSTRAINT UQ_PROFILES_PROFILE_NAME UNIQUE (PROFILE_NAME),
CONSTRAINT PK_PROFILES_ID_PROFILE PRIMARY KEY (ID_PROFILE));"

This is the error:

"ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server ver sion for the right syntax to use near 'CONSTRAINT NN_PROFILES_PROFILE_NAME NOT NULL, CONSTRAINT UQ_PROFILES_PROFILE_NAM' at line 3"

Upvotes: 0

Views: 1459

Answers (1)

Rick James
Rick James

Reputation: 142366

There are a thousand syntax differences between Oracle and MySQL. You must consult the MySQL reference manual.

CREATE TABLE TBLPROFILES(                       -- ok
    ID_PROFILE INT(3) UNSIGNED AUTO_INCREMENT,  -- ok
    PROFILE_NAME VARCHAR(10)  NOT NULL,         -- note 1
    UNIQUE (PROFILE_NAME),                      -- note 2
    PRIMARY KEY (ID_PROFILE));                  -- note 2

Note 1: NOT NULL is not really a "constraint"; it is part of the column definition.

Note 2: I provided a simpler syntax. (Possibly the Oracle syntax would also work; I don't know.)

Other notes:

INT(3) -- the 3 is ignored unless you have ZEROFILL. An INT is always a 32-bit number. (I think this differes from Oracle.)

All-caps is not normally used in MySQL for table and column names. (But it does work, and such are always case-folded.)

Disclaimer: No, I don't know exactly how many differences there are.

Upvotes: 0

Related Questions