Tech Off Thread

5 posts

How do you set up a foreign key in SQL Azure?

Back to Forum: Tech Off
  • complete

    How do you set up a foreign key in SQL Azure?

    I have a database now on the Azure system.

    The tables in the database have an intended use such that there is one main, logically centralized table and then there are a number of tables which each describe some column in the main table.  For example, if I have an age classification column in the main table, I might have a separate table which lists some categories of the age classification (as a string) and some associated index.  Then the index from the age classification table would be used as a reference in the main table.

    Is this what a foreign key is?  Is there some way in SQL Azure to make the association between these indexes?

  • blowdart

    The same way as you would in local SQL surely? (unless you're using table storage of course, that's just a huge blob)

  • complete

    is that done on T-SQL?  I think it has to be done with the Management Studio.

    http://www.youtube.com/watch?v=QDIHfE5utkk

    Generic Forum Image

    The youtube video instructs to use this syntax.  But what is the constraint?  Which table should be altered -- the main one or the index table?

    This instructional video does not explain how it is to be done.  It shows how to create the required three constraints but nothing beyond that.  So, what else do I do?

    Generic Forum Image

    Generic Forum Image

    Generic Forum Image

     

     

  • JohnAskew

    I use SSMS and manually create the database tables, etc., most often, for smaller projects or hobbies.

    Enterprise-wide databases are designed by committee or is a hold over from the Paleolithic era.

    You will find examples online for the DB script approach if that is all you have to use... hopefully you have SSMS?

  • blowdart

    , complete wrote

    is that done on T-SQL?  I think it has to be done with the Management Studio.

    http://www.youtube.com/watch?v=QDIHfE5utkk

    Generic Forum Image

    The youtube video instructs to use this syntax.  But what is the constraint?  Which table should be altered -- the main one or the index table?

    This instructional video does not explain how it is to be done.  It shows how to create the required three constraints but nothing beyond that.  So, what else do I do?

    Generic Forum Image

    Generic Forum Image

    Generic Forum Image

     

     

    I don't use management studio, except to run SQL Wink


    So a constraint is, errr, a constraint on what data can go into a table. A primary key is a constraint because it tells SQL to constrain the values in the column to be unique, no duplicates.

    A foreign key tells SQL to constrain values in a column to only allow values which exist in another column in another table.


    So you first set up a primary key on the master column, to take one of yours, age classification. Let's assume it has an AgeId and a Description field; you'd do

    ALTER TABLE AgeClassificationADD CONSTRAINT PK_AgeClassificationPRIMARY KEY CLUSTERED(AgeId)

    Then you need to setup the foreign key. I will assume that your primary table is called People, and it also has an AgeID field, which you want to be the link between the two.

    ALTER TABLE PeopleADD CONSTRAINT FK_People_AgeClassification_AgeIdFOREIGN KEY (AgeId)REFERENCES AgeClassification (AgeId)

    So the line FOREIGN KEY (AgeId) specifies the field you are going to link to another table. REFERENCES gives the other table name and then, in brackets, the field in the other table.

    Does that help?

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.