Foreign key with a constant

[WP]

I have two tables with data ...

Exchange
EXCID DESCRIPTION
XLON London
XMAD Madrid


ExchangeMap
CODEID MAPID ISSUER NAME
100 55 REUTERS L
101 55 MIC XLON
103 56 REUTERS MA
104 56 MIC

I'd like to show the relationship between the two tables. Without the issuer on my exchange table can I have a foreign key where instead of a field you use a constant string?

i.e. Exchangemap.issuer='MIC', Exchangemap.name=Exchange.EXCID

 

[carp]

What happened when you tried to do this?

 

[WP]

Well ...

alter table ExchangeMap add constraint FK_EXC foreign key (issuer,value) references OSMEXC ('MIC',EXCID);

didn't work but I wasn't sure if there was new syntax for using a constant instead of a field

WP

http://www.neptune400.com

 

[Dagon]

No, I don't think you can use a constant like that. You'd either have to add the column and value to OSMEXC or add a check constraint to limit the range of values of Issuer to just 'MIC'.