CODEWITHSUNDEEP
mysqlsqlpostgresqlsyntax
Pinteco
Pinteco

Reputation: 319

Syntax error with CONSTRAINT on MySQL that worked on postgres

The professor gave a .sql file to do some exercises, here is a piece of it:

CREATE TABLE DEPT

   (DEPTNO NUMERIC(2) CONSTRAINT PK_DEPT PRIMARY KEY, (error 1)
DNAME VARCHAR(14),
LOC VARCHAR(13) );

CREATE TABLE EMP

   (EMPNO NUMERIC(4) CONSTRAINT PK_EMP PRIMARY KEY, (error 2)
ENAME VARCHAR(10),
JOB VARCHAR(9),
HIREDATE DATE,
SAL NUMERIC(7,2),
DEPTNO NUMERIC(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT); (error 3)

We runned the file in class with pgadmin 3 and everything went fine, but I downloaded MySQL instead of postgres to continue the exercises at home and it gives an error: "unexpected constraint". Since it works with postgres but not in MySQL maybe is a syntax error with MySQL?

Upvotes: 0

Views: 383

Answers (2)

dwir182
dwir182

Reputation: 1549

You should do more verbose, And every rdbms are different.. You can't use statement you work in some rdbms to other rdbms

Table Dept

CREATE TABLE DEPT
(DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13), 
CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO));

Table Emp

CREATE TABLE EMP
(EMPNO NUMERIC(4),
ENAME VARCHAR(10),
JOB VARCHAR(9),
HIREDATE DATE,
SAL NUMERIC(7,2),
DEPTNO NUMERIC(2), 
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));

Upvotes: 1

Ian
Ian

Reputation: 422

MySQL's acceptable formats for foreign keys are described within their extensive documentation.

Other CONSTRAINT options are also shown in the documents

For example, PRIMARY KEY:

[CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...)
      [index_option] ...

As part of a CREATE TABLE, this can go at the end for FOREIGN KEY checks.

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Upvotes: 0

Related Questions