foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
'''''?Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> '''''?|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
>> I want to make a foreign key relationship between two tables but the key
> is
>> multiple fields. I am getting an error message when I try.
>> '''''?
>|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT'.
"sql" wrote:
> I want to make a foreign key relationship between two tables but the key is
> multiple fields. I am getting an error message when I try.
> '''''?|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.
>
> "sql" wrote:
> > I want to make a foreign key relationship between two tables but the key
is
> > multiple fields. I am getting an error message when I try.
> > '''''?