Need help adding foreign key constraint

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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