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?

[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: nm*@acm.org |Security with GnuPG: |
|ERHVERVSAKADEMIET |http://x15.dk |keyID: 0xD4DB4A5E at |
|Aarhus Business College|phone: [phone] |http://www.pgp.net |
|Sønderhøj 30, Viby J. |cell: [phone] | |
|DK8260 DENMARK |fax: [phone] | |
+-----------------------+---------------------+----------------------+

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

[Gaetano Mendola]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

John Haney wrote:

| 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);

You forgot to specify the data type for the field Type.


Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBGVHq7UpzwH2SGd4RAriNAKDRKJCpgGen8VVsxg//rmjqU+O6vgCg4u/9
9zcAUYNCfaeU2i9WVTXdh3k=
=5YeH
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend