Create Table with Foreign Key Error

[John Haney]

Postgresql 7.4.3-1 under Cygwin.

I created a table called ServerTypes:

CREATE TABLE ServerTypes(
ServerTypeID SERIAL UNIQUE NOT NULL,
Type TEXT PRIMARY KEY);

Works fine.

Now, I want to create a table called servers with a Foreign Key
referencing ServerTypes.Type:

CREATE TABLE Servers(
ServerID SERIAL UNIQUE NOT NULL,
Type REFERENCES ServerTypes (Type),
Server TEXT PRIMARY KEY);

This command produces the following error:

ERROR: syntax error at or near "REFERENCES" at character 61

I see nothing wrong with the syntax and I'm baffled. Anyone have any
idea why this isn't working?

[John Haney]

[Niels Muller Larsen]

On Tue, 10 Aug 2004, John Haney wrote:

>
> CREATE TABLE Servers(
> ServerID SERIAL UNIQUE NOT NULL,
> Type REFERENCES ServerTypes (Type),
> Server TEXT PRIMARY KEY);
>

> I see nothing wrong with the syntax and I'm baffled. Anyone have any
> idea why this isn't working?
>

The offending line must be:

Type TEXT REFERENCES ServerTypes (Type),

you cannot declare the datatype through the referencing, you must
declare a datatype and then reference to the other table

Virtually

Niels Müller Larsen
M. Sc. Networked Information Engineering
+-----------------------+---------------------+----------------------+
|Multimedia Department |mailto: n...@acm.org |Security with GnuPG: |
|ERHVERVSAKADEMIET |http://x15.dk |keyID: 0xD4DB4A5E at |
|Aarhus Business College|phone: +45 8936 3317 |http://www.pgp.net |
|Sønderhøj 30, Viby J. |cell: +45 2040 5740 | |
|DK8260 DENMARK |fax: +45 8936 3399 | |
+-----------------------+---------------------+----------------------+

"Our vision is to speed up time, eventually eliminating it."
-- Alex Schure