Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

TVP insertion

i have a bunch of user-defined table TYPES, to which i am doing table-valued parm insertions.

suddenly the table def needs to change, and i need to put FK's on two values within the target tables, that are written to with these TVP's.

i can put FK's on the target tables, right?  they needn't go on the TYPE tables, too, do they?
what's the best way to handle that?
  • 2
1 Solution
Raja Jegan RSQL Server DBA & ArchitectCommented:
You can't create Foreign Key constraints on a Type table.
You would be able to create just Primary and Unique Keys constraints on a type table and not Foreign Keys..

And may I know the exact thing why you went for a user defined table type instead of a table..
dbaSQLAuthor Commented:
yes, i realize they can't go onto the TYPEs, but i'm just saying that's not really a big deal, and I'd put them onto the regular tables themselves.  right?

i didn't go for a table type instead of a table, i am inserting into a regular table via the table types, simply for massive data inserts.  i use the table-valued-parameter insert thru my table types, to do the large inserts in fewer round trips from client to server

i know i can put unique constraints or pk's onto the table types, but i'm just wondering... is it sufficient merely to put the FK's into place on the target table

going to try it out now
dbaSQLAuthor Commented:
yep.  seems to work just fine.  though i answered my own inquiry, i will award and close.  thank you for looking

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now