What would my SQL statement be to insert "Arnold Schwarzenegger" and "Hasta la vista baby" into these two empty SQL tables?

What would my statement be to insert "Arnold Schwarzenegger" and "Hasta la vista baby" into the following empty SQL tables?

The title of this question was originally going to be "How to insert the first records into SQL tables with bidirectional associations and autogenerated integer PK?" but I wasn't sure if I was phrasing that correctly... Basically, I have two tables Actors and CatchPhrases.

Actors looks like:

  ActorId                  int             NOT NULL  PK (autogenerated by db)
  FavoriteCatchPhraseId    int             NOT NULL  FK
  Name                     varchar(200)    NOT NULL

CatchPhrases looks like:

  CatchPhraseId            int             NOT NULL  PK (autogenerated by db)
  ActorId                  int             NOT NULL  FK
  PhraseText               varchar(500)    NOT NULL

S开发者_Go百科o, Actors can have multiple catch phrases but must have at least one. A catch phrase is associated with an actor. There is currently no data in either table.

---

I would model it differently to avoid a bidirectional relation (which would be difficult to do). Simply add a column (IsFavorite) to the CatchPhrases table. Either use a constraint or business rule in code to limit the number of catch phrases marked as a favorite for each actor to one.

Actors:

ActorId                  int             NOT NULL  PK (autogenerated by db)
Name                     varchar(200)    NOT NULL

CatchPhrases:

CatchPhraseId            int             NOT NULL  PK (autogenerated by db)
ActorId                  int             NOT NULL  FK
PhraseText               varchar(500)    NOT NULL
IsFavorite               bit             NOT NULL

Make sure that you have an index on ActorId for the CatchPhrases table so you can quickly find the actor's catch phrases.

Alternatively, using a join table -- which would allow multiple actors to have the same catch phrase.

Actors:

ActorId                  int             NOT NULL PK (autogenerated by db)
Name                     varchar(200)    NOT NULL

ActorCatchPhrases

ActorId                  int             NOT NULL PK (FK to Actors)
CatchPhraseId            int             NOT NULL PK (FK to CatchPhrases)
IsFavorite               bit             NOT NULL

CatchPhrases

PhraseId                 int             NOT NULL PK (autogenerated by db)  
PhraseText               varchar(500)    NOT NULL

---

The rule, namely that the parent record must have at least one child record, cannot be enforced with declarative referential integrity.

"Favorite" is a singular, and therefore FavoriteCatchPhrase could simply be an attribute of the Actor entity, i.e. a column in the Actors table. You could store the text of the phrase. But if you wanted to enforce the rule that the favorite catch phrase must come from a set of bona-fide catch phrases, phrases that have been vetted and acknowledged to be truly "catchy" and not merely some not-so-memorable saying, then you would have a CatchPhrases table and you could have Actor.FavoriteCatchPhrase store the phrase id and reference CatchPhrases table as a foreign key, though more than one actor could use the same catchphrase unless you put a unique index on Actor.FavoriteCatchPhrase.

---

I think you can do this within a transaction, by using a 'dirty read' (read uncommitted).

But its not very nice even if possible.

As @tvanfosson suggested the cleanest way would be to turn off the FK on ActorId column of the CatchPhrases table.

First create a dummy CatchPhrases (set identity insert on) row like:

0 - 0 - 'No favourite catchphrase', 0

Then when you want to insert an Actors row there is a default to use:

(identity) - 0 - 'Arnold Shwarzenegger'

Then set a variable to the @@identity value that the Actors insert will have generated

Then the catchphrase:

(identity) - (variable) - 'Hasta la vista baby'

Then set a variable to the @@identity value that the CatchPhrases insert will have generated and use it to update the catchphrase id in the actors row.

... phew, are you sure this design is right?

EDIT

Well if we can change the design ...

Looking at the relationships, Actors can have many Catchphrases and Catchphrases can have many Actors. So there is a many-to-many design - which is usually refactored using a link entity (MSDN calls this a junction table):

Actors   
  |
-----
| | |
ActorsCatchphrases
| | |
-----
  |
Catchphrases

• Actors has the Actor Name and Actor details (with no references to catchphrases)
• ActorsCatchphrases has the ActorId and the CatchphraseId and the boolean as to whether it is the favourite of that actor
• Catchphrases has the details of the catchphrase (with no reference to an actor)