ForiegnKey - Server: Msg 1776 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

ForiegnKey - Server: Msg 1776

Author

Topic

anizami
Starting Member

4 Posts

Posted - 2005-04-08 : 13:07:53

HI
I am having problem creating a table with foreign keys which are referencing a table which is linked to another table. here is what im doing and its giving me an error. can someone help pls.

Create table ExchangeSymbol (
ExchangeName varchar(20) NOT NULL,
Symbol varchar(10) NOT NULL ,
ExSymbol varchar(10) NOT NULL,
Constraint FK_COMEX Foreign Key (Symbol) References Commodity (Symbol)
ON DELETE NO ACTION ON UPDATE CASCADE,
Constraint FK_COMEXii Foreign Key(ExchangeName) References Exchange(ExchangeName)
ON UPDATE CASCADE,
Constraint PK_COMEX Primary Key(ExSymbol,ExchangeName)

)

Create Table Margins(
ExchangeName varchar(20) NOT NULL ,
ExSymbol varchar(10) NOT NULL,
Constraint FK_M Foreign Key (ExchangeName) References ExchangeSymbol(ExchangeName),
Constraint FK_M2 Foreign Key (ExSymbol) References ExchangeSymbol(ExSymbol),
Constraint PK_M Primary Key (ExSymbol,ExchangeName)
)
Go

The ExchangeName and ExSymbol together are unique keys but seperate can ocur multiple times.

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced table 'ExchangeSymbol' that match the referencing column list in the foreign key 'FK_M'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-08 : 13:27:46

a foreign key needs to reference columns that are part of a primary key or a unique constraint so this would work:

Create Table Margins(
ExchangeName varchar(20) NOT NULL ,
ExSymbol varchar(10) NOT NULL,
--Constraint FK_M Foreign Key (ExchangeName) References ExchangeSymbol(ExchangeName),
--Constraint FK_M2 Foreign Key (ExSymbol) References ExchangeSymbol(ExSymbol),
Constraint FK_M2 Foreign Key (ExSymbol,ExchangeName) References ExchangeSymbol(ExSymbol,ExchangeName),
Constraint PK_M Primary Key (ExSymbol,ExchangeName)
)

Be One with the Optimizer
TG

anizami
Starting Member

4 Posts

Posted - 2005-04-08 : 14:38:18

thanks!!! it worked...:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources