locked
Need help adding foreign key constraint RRS feed

  • Question

  • Here is my sqlce:

    Code Block

    alter table tblV
    add constraint
    foreign key (T_ID)
    references tblT(T_ID)
    on delete set null on update cascade


    Error is:
    Major Error 0x80040E14, Minor Error 25501
    There was an error parsing the query. [ Token line number = 3,Token line offset = 1,Token in error = foreign ]

    I'm new with SQL Mobile, so sorry for the novice question.

    Thanks
    Thursday, December 13, 2007 6:07 PM

Answers

  • If you want to specify a constraint name then the syntax is as shown below.

     

    Code Block

    ALTER TABLE <TableName>

        ADD CONSTRAINT <ConstraintName> FOREIGN KEY (<Columns>) REFERENCES
        <RefTables> (<RefColumns>)

     

    In case you want the database to generate the constraint name for you. Then the syntax of the query is as shown below.

     

    Code Block

    ALTER TABLE <TableName>

        ADD FOREIGN KEY (<Columns>) REFERENCES
        <RefTables> (<RefColumns>)

     

    The documentation is proper in this respect.
    Monday, December 24, 2007 5:26 AM
    Answerer

All replies

  • I also tried to change the syntax a bit to see if that was the problem.

    alter table [tblV] add constraint foreign key ([T_ID])
    references tblT([T_ID])
    on delete set null
    on update cascade


    But I got the same errors...
    Thursday, December 13, 2007 6:37 PM
  • Try this:

    ALTER TABLE tblV ADD CONSTRAINT tblV_ForeignKey_1 FOREIGN KEY (T_ID)
    REFERENCES tblT(T_ID) ON DELETE SET NULL ON UPDATE CASCADE;



    The caps don't matter, but you needed an ID for the constraint...each constraint needs a unique ID.
    Thursday, December 13, 2007 7:05 PM
  • I tried that and it worked but I wanted the database to create a name for me. The documentation says this:

    If the constraint_name is not specified, SQL Server Compact Edition generates a constraint name.

    But apparently that is false.

    http://msdn2.microsoft.com/en-us/library/ms174123.aspx
    Thursday, December 13, 2007 7:20 PM
  • If you want to specify a constraint name then the syntax is as shown below.

     

    Code Block

    ALTER TABLE <TableName>

        ADD CONSTRAINT <ConstraintName> FOREIGN KEY (<Columns>) REFERENCES
        <RefTables> (<RefColumns>)

     

    In case you want the database to generate the constraint name for you. Then the syntax of the query is as shown below.

     

    Code Block

    ALTER TABLE <TableName>

        ADD FOREIGN KEY (<Columns>) REFERENCES
        <RefTables> (<RefColumns>)

     

    The documentation is proper in this respect.
    Monday, December 24, 2007 5:26 AM
    Answerer