Defining Foreign keys in Management Studio

Hi,
I am using SQL Server 2005.
I have created the tables for my Database through the Management Studio
front end tool.
I can apply a primary key to the tables easily enough. What I am having
trouble with is assigning multiple columns as primary keys and also how to
define foreign keys between tables.
Does anyone have suggesstions how I can achieve this through management
studio?
Thanks In Advance
MaccaOn Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>Hi,
>I am using SQL Server 2005.
>I have created the tables for my Database through the Management Studio
>front end tool.
>I can apply a primary key to the tables easily enough. What I am having
>trouble with is assigning multiple columns as primary keys
Hi Macca,
Easiest: open a new query window, type
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable -- Or any other name
PRIMARY KEY (Col1, Col2, Col3)
Then, click the Execute button.
But if you prefer to use point and cllick, just hold down the
Ctrl-button on your keyboard while selecting key columns, than
right-click and choose "Set Primary Key".
> and also how to
>define foreign keys between tables.
Again, the easiest is to just type and execute the SQL command:
ALTER TABLE ReferingTable
ADD CONSTRAINT LogicalNameGoesHere
FOREIGN KEY (Col1, Col2, Col3)
REFERENCES ReferedTable (Col1, Col2, Col3)
Optionally, add an ON UPDATE and/or ON DELETE clause.
Using point and click: rightclick refering table and choose "Modify".
Click menu-item "Table Designer" / "Relationships". Click "Add". Under
"(General)", find the entry for "Tables and Columns Specification",
click in the emppty field next to it, then click the smalll ellipsis
button. Enter a name for the foreign key constraint. Then, on the right
hand side, use the drop down lists to select the column(s) that form the
relationship. Move to the left-hand side, choose the refered table and
choose the columns that are refered to. Click "OK" to save.
Regardless of whether you use SQL or point and click to set the
relationship, the column(s) in the refered table MUST be set as either a
PRIMARY KEY or a UNIQUE constraint.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks for the reply. I have a question with the SQl that creates a foreign
key.
If I have Table1 and Table 2 and Table 1 has a foreign key which is the
Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
query?
Thanks
Macca
"Hugo Kornelis" wrote:
> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
> >Hi,
> >
> >I am using SQL Server 2005.
> >
> >I have created the tables for my Database through the Management Studio
> >front end tool.
> >
> >I can apply a primary key to the tables easily enough. What I am having
> >trouble with is assigning multiple columns as primary keys
> Hi Macca,
> Easiest: open a new query window, type
> ALTER TABLE MyTable
> ADD CONSTRAINT PK_MyTable -- Or any other name
> PRIMARY KEY (Col1, Col2, Col3)
> Then, click the Execute button.
> But if you prefer to use point and cllick, just hold down the
> Ctrl-button on your keyboard while selecting key columns, than
> right-click and choose "Set Primary Key".
> > and also how to
> >define foreign keys between tables.
> Again, the easiest is to just type and execute the SQL command:
> ALTER TABLE ReferingTable
> ADD CONSTRAINT LogicalNameGoesHere
> FOREIGN KEY (Col1, Col2, Col3)
> REFERENCES ReferedTable (Col1, Col2, Col3)
> Optionally, add an ON UPDATE and/or ON DELETE clause.
> Using point and click: rightclick refering table and choose "Modify".
> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
> "(General)", find the entry for "Tables and Columns Specification",
> click in the emppty field next to it, then click the smalll ellipsis
> button. Enter a name for the foreign key constraint. Then, on the right
> hand side, use the drop down lists to select the column(s) that form the
> relationship. Move to the left-hand side, choose the refered table and
> choose the columns that are refered to. Click "OK" to save.
> Regardless of whether you use SQL or point and click to set the
> relationship, the column(s) in the refered table MUST be set as either a
> PRIMARY KEY or a UNIQUE constraint.
> --
> Hugo Kornelis, SQL Server MVP
>|||> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
> query?
It is not the query that describes which table is the referencing or the referenced table. It is the
data model. In the data model you describe, Table2 is the referenced table and Table 1 is the
referencing table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Macca" <Macca@.discussions.microsoft.com> wrote in message
news:F44BB119-F24C-4146-91CB-0586510C1589@.microsoft.com...
> Hi Hugo,
> Thanks for the reply. I have a question with the SQl that creates a foreign
> key.
> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
> query?
> Thanks
> Macca
> "Hugo Kornelis" wrote:
>> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>> >Hi,
>> >
>> >I am using SQL Server 2005.
>> >
>> >I have created the tables for my Database through the Management Studio
>> >front end tool.
>> >
>> >I can apply a primary key to the tables easily enough. What I am having
>> >trouble with is assigning multiple columns as primary keys
>> Hi Macca,
>> Easiest: open a new query window, type
>> ALTER TABLE MyTable
>> ADD CONSTRAINT PK_MyTable -- Or any other name
>> PRIMARY KEY (Col1, Col2, Col3)
>> Then, click the Execute button.
>> But if you prefer to use point and cllick, just hold down the
>> Ctrl-button on your keyboard while selecting key columns, than
>> right-click and choose "Set Primary Key".
>> > and also how to
>> >define foreign keys between tables.
>> Again, the easiest is to just type and execute the SQL command:
>> ALTER TABLE ReferingTable
>> ADD CONSTRAINT LogicalNameGoesHere
>> FOREIGN KEY (Col1, Col2, Col3)
>> REFERENCES ReferedTable (Col1, Col2, Col3)
>> Optionally, add an ON UPDATE and/or ON DELETE clause.
>> Using point and click: rightclick refering table and choose "Modify".
>> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
>> "(General)", find the entry for "Tables and Columns Specification",
>> click in the emppty field next to it, then click the smalll ellipsis
>> button. Enter a name for the foreign key constraint. Then, on the right
>> hand side, use the drop down lists to select the column(s) that form the
>> relationship. Move to the left-hand side, choose the refered table and
>> choose the columns that are refered to. Click "OK" to save.
>> Regardless of whether you use SQL or point and click to set the
>> relationship, the column(s) in the refered table MUST be set as either a
>> PRIMARY KEY or a UNIQUE constraint.
>> --
>> Hugo Kornelis, SQL Server MVP