Click here to monitor SSC

Exception to foreign key constraint

Last post 04-08-2009, 9:28 AM by Arles. 1 replies.
Sort Posts: Previous Next
  •  03-11-2009, 5:19 PM Post number 72444

    • ebh is not online. Last active: 04-01-2009, 4:48 PM ebh
    • Not Ranked
    • Joined on 06-10-2008
    • Level 1: Deep thought

    Exception to foreign key constraint

    We have two tables:

    create table Currency (
        CurrencyID CHAR(3) not null primary key,
        Name varchar(70) not null)

    Create table CurrencyClosed (
        CurrencyID CHAR(3) not null,
        StartDateTime datetime not null,
        EndDateTime datetime not null,
        Desciption varchar(1000) not null)

    Obviously we want this foreign key constraint:

    alter table CurrencyClosed
     add constraint FK_CurrencyClosed__CurrencyID
     foreign key (CurrencyID)
     references Currency(CurrencyID)

    However we also want to have rows in CurrencyClosed that represent when every currency is closed. What is the best method to do this? What are the pros & cons of the various different options?
  •  04-08-2009, 9:28 AM Post number 72777 in reply to post number 72444

    • Arles is not online. Last active: 02-23-2011, 7:26 AM Arles
    • Top 25 Contributor
    • Joined on 10-20-2008
    • Nashville, TN
    • Celeron 450

    Re: Exception to foreign key constraint

    Could you clarify what you mean by "when every currency is closed".  Looking at your table structure for "CurrencyClosed", it seems that you're already collecting the date/time that the currency closed.  Are you referring to the date/time and time when the currency last closed?  If so, a "MAX()" or "RANK() OVER" should get you what you need.  If not, could you please give me some more explanation or sample of what you're needing.

View as RSS news feed in XML