FOREIGN KEY

[ecbrown]

Hi All,

I am trying to get started with Suppliers and Parts. When I use this command (found in Date's SQL and Relational Theory, 2nd edition page 86), I get an error for relation SP.

ERROR: Encountered "FOREIGN" at line 27, column 1.
Was expecting one of:     "KEY" ...     ";" ...

Any advice would be appreciated.

Best regards,
Eric

------------
My input:

VAR S BASE RELATION
{
SNO CHAR,
SNAME CHAR,
STATUS INTEGER,
CITY CHAR
}
KEY { SNO } ;

VAR P BASE RELATION
{
PNO CHAR,
PNAME CHAR,
COLOR CHAR,
WEIGHT RATIONAL,
CITY CHAR
}
KEY { PNO } ;

VAR SP BASE RELATION
{
SNO CHAR,
PNO CHAR,
QTY INTEGER
}
KEY { SNO, PNO }
FOREIGN KEY { SNO }
REFERENCES S
FOREIGN KEY { PNO }
REFERENCES P ;

[Dave]

"FOREIGN KEY" is not an official part of Tutorial D syntax. Hence, it is not implemented in Rel, especially as "FOREIGN KEY" is simply one instance of a general category of constraints.

In the Scripts folder of a Rel installation, you'll find a script called DateBookSampleRelvars.d that implements Suppliers and Parts, including the appropriate constraints. "FOREIGN KEY {S#} REFERENCES S" is written as "CONSTRAINT SP_FKEY1 SP {S#} <= S {S#}", where '<=' is the IS_SUBSET_OF operator.

[ecbrown]

Thanks for your quick and helpful response. I found the scripts!

[uyar]

Hi,

I'm trying to figure out if I can integrate Rel into my database management systems course. When adapting my example database to Rel, I've had a problem with foreign keys. It seems to me that the attribute names have to be the same in both relations. My definitions are below:

Code: Select all

TYPE MOVIE# POSSREP { VALUE INTEGER };
TYPE PERSON# POSSREP { VALUE INTEGER };

VAR MOVIE REAL RELATION
    { MOVIE# MOVIE#, TITLE CHAR, DIRECTOR# PERSON# }
    KEY { MOVIE# };

VAR PERSON REAL RELATION
    { PERSON# PERSON#, NAME CHAR }
    KEY { PERSON# };

CONSTRAINT MOVIE_FKEY_DIRECTOR MOVIE { DIRECTOR# } <= PERSON { PERSON# };

When I execute this, I get:

ERROR: Attribute DIRECTOR# not found in {PERSON# PERSON#}

Of course I could solve this by renaming the attribute to PERSON# but I would like to use self-explanatory names and the syntax seems to support it. Am I doing something wrong? Thanks.

[Dave]

You can use the RENAME operator in the MOVIE_FKEY_DIRECTOR constraint.

The expression MOVIE { DIRECTOR# } <= PERSON { PERSON# } asks if the result of MOVIE { DIRECTOR# } is a subset of PERSON { PERSON# }. The former is of type RELATION { DIRECTOR# PERSON# }, but the latter is of type RELATION { PERSON# PERSON# }. Because the attribute names differ, they are different types.

The '<=' operator requires that both operands be the same type, so you probably want the constraint to be:

Code: Select all

CONSTRAINT MOVIE_FKEY_DIRECTOR MOVIE { DIRECTOR# } RENAME (DIRECTOR# AS PERSON#) <= PERSON { PERSON# };

[uyar]

OK, I get it. Thanks.