Syntax error with CONSTRAINT on MySQL that worked on postgres











up vote
0
down vote

favorite












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?










share|improve this question




















  • 1




    Every DB engine uses a slightly different syntax. You can't just run the same SQL code on all engines.
    – juergen d
    Nov 10 at 21:21










  • MySQL supports various syntaxes, but apparently not that one. I'm use to the more verbose form CONSTRAINT name FOREIGN KEY (columnOfThisTable) REFERENCES ThatTable (columnOfThatTable). I think that works in MySQL and most other databases. Not sure about Postgres, but probably there too.
    – GolezTrol
    Nov 10 at 21:44















up vote
0
down vote

favorite












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?










share|improve this question




















  • 1




    Every DB engine uses a slightly different syntax. You can't just run the same SQL code on all engines.
    – juergen d
    Nov 10 at 21:21










  • MySQL supports various syntaxes, but apparently not that one. I'm use to the more verbose form CONSTRAINT name FOREIGN KEY (columnOfThisTable) REFERENCES ThatTable (columnOfThatTable). I think that works in MySQL and most other databases. Not sure about Postgres, but probably there too.
    – GolezTrol
    Nov 10 at 21:44













up vote
0
down vote

favorite









up vote
0
down vote

favorite











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?










share|improve this question















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?







mysql sql postgresql syntax






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 10 at 21:57

























asked Nov 10 at 21:20









Pinteco

1114




1114








  • 1




    Every DB engine uses a slightly different syntax. You can't just run the same SQL code on all engines.
    – juergen d
    Nov 10 at 21:21










  • MySQL supports various syntaxes, but apparently not that one. I'm use to the more verbose form CONSTRAINT name FOREIGN KEY (columnOfThisTable) REFERENCES ThatTable (columnOfThatTable). I think that works in MySQL and most other databases. Not sure about Postgres, but probably there too.
    – GolezTrol
    Nov 10 at 21:44














  • 1




    Every DB engine uses a slightly different syntax. You can't just run the same SQL code on all engines.
    – juergen d
    Nov 10 at 21:21










  • MySQL supports various syntaxes, but apparently not that one. I'm use to the more verbose form CONSTRAINT name FOREIGN KEY (columnOfThisTable) REFERENCES ThatTable (columnOfThatTable). I think that works in MySQL and most other databases. Not sure about Postgres, but probably there too.
    – GolezTrol
    Nov 10 at 21:44








1




1




Every DB engine uses a slightly different syntax. You can't just run the same SQL code on all engines.
– juergen d
Nov 10 at 21:21




Every DB engine uses a slightly different syntax. You can't just run the same SQL code on all engines.
– juergen d
Nov 10 at 21:21












MySQL supports various syntaxes, but apparently not that one. I'm use to the more verbose form CONSTRAINT name FOREIGN KEY (columnOfThisTable) REFERENCES ThatTable (columnOfThatTable). I think that works in MySQL and most other databases. Not sure about Postgres, but probably there too.
– GolezTrol
Nov 10 at 21:44




MySQL supports various syntaxes, but apparently not that one. I'm use to the more verbose form CONSTRAINT name FOREIGN KEY (columnOfThisTable) REFERENCES ThatTable (columnOfThatTable). I think that works in MySQL and most other databases. Not sure about Postgres, but probably there too.
– GolezTrol
Nov 10 at 21:44












2 Answers
2






active

oldest

votes

















up vote
1
down vote



accepted










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));





share|improve this answer























  • Now only the last line that doesn't work. But the other two errors vanished.
    – Pinteco
    Nov 10 at 22:13










  • @Pinteco my bad.. You can check up again.. :)
    – dwir182
    Nov 10 at 22:15


















up vote
0
down vote













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





share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53243520%2fsyntax-error-with-constraint-on-mysql-that-worked-on-postgres%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    1
    down vote



    accepted










    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));





    share|improve this answer























    • Now only the last line that doesn't work. But the other two errors vanished.
      – Pinteco
      Nov 10 at 22:13










    • @Pinteco my bad.. You can check up again.. :)
      – dwir182
      Nov 10 at 22:15















    up vote
    1
    down vote



    accepted










    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));





    share|improve this answer























    • Now only the last line that doesn't work. But the other two errors vanished.
      – Pinteco
      Nov 10 at 22:13










    • @Pinteco my bad.. You can check up again.. :)
      – dwir182
      Nov 10 at 22:15













    up vote
    1
    down vote



    accepted







    up vote
    1
    down vote



    accepted






    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));





    share|improve this answer














    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));






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 10 at 22:15

























    answered Nov 10 at 21:58









    dwir182

    1,896318




    1,896318












    • Now only the last line that doesn't work. But the other two errors vanished.
      – Pinteco
      Nov 10 at 22:13










    • @Pinteco my bad.. You can check up again.. :)
      – dwir182
      Nov 10 at 22:15


















    • Now only the last line that doesn't work. But the other two errors vanished.
      – Pinteco
      Nov 10 at 22:13










    • @Pinteco my bad.. You can check up again.. :)
      – dwir182
      Nov 10 at 22:15
















    Now only the last line that doesn't work. But the other two errors vanished.
    – Pinteco
    Nov 10 at 22:13




    Now only the last line that doesn't work. But the other two errors vanished.
    – Pinteco
    Nov 10 at 22:13












    @Pinteco my bad.. You can check up again.. :)
    – dwir182
    Nov 10 at 22:15




    @Pinteco my bad.. You can check up again.. :)
    – dwir182
    Nov 10 at 22:15












    up vote
    0
    down vote













    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





    share|improve this answer

























      up vote
      0
      down vote













      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





      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        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





        share|improve this answer












        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






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 10 at 22:01









        Ian

        10010




        10010






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53243520%2fsyntax-error-with-constraint-on-mysql-that-worked-on-postgres%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Florida Star v. B. J. F.

            Danny Elfman

            Lugert, Oklahoma