Advertisement
jamiet

ComposableDML cannot insert into a table with a FOREIGN KEY

Aug 10th, 2012
226
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.73 KB | None | 0 0
  1. /*This code demonstrates the fact that you can't use composable DML to insert into a table that has a FK*/
  2. USE tempdb
  3. CREATE TABLE [TableWithPK] ([col1] INT PRIMARY KEY);
  4. CREATE TABLE [TableWithFK] ([col1] INT REFERENCES [TableWithPK]([col1]));
  5. /*Following statement fails with error:
  6. Msg 356, Level 16, State 1, Line 17
  7. The target table 'TableWithFK' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the
  8. FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'FK__TableWithF__col1__2D27B809'.
  9. */
  10. INSERT  [TableWithFK]([col1])
  11. SELECT  [col1]
  12. FROM    (
  13.         INSERT  [TableWithPK]([col1])
  14.         OUTPUT  [INSERTED].[col1]
  15.         VALUES(1)
  16.         )insert_out
  17. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement