CREATE TABLE query help!

[asandino]

I am currently studying Computer Science and I am taking a Databases course on SQL that requires me to use OpenOffice I am trying to run the following code:

CREATE TABLE "Patient" (
"PatientID" int NOT NULL ,
"Name" varchar(100) NOT NULL,
"Phone" varchar(15) NULL,
"Email" varchar(30) NULL,
"Address" varchar(150) NOT NULL,
"AddedDate" date NULL,
"DoctorID" int,
PRIMARY KEY ("PatientID"),
CONSTRAINT "DoctorForeign" FOREIGN KEY ("DoctorID") REFERENCES "Doctor" ("DoctorID"),
) ;

and I keep getting an error that says

CONSTRAINT "DoctorForeign" FOREIGN KEY ("DoctorID") REFERENCES "Doctor"]

and to be honest I don't know where the mistake is, I would appreciate if someone could help me,

Thanks

[Villeroy]

Your database is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html
Create the table first, then add the constraint.

Code: Select all

CREATE TABLE "foo"(...);
ALTER TABLE "foo" ADD CONSTRAINT ...

[asandino]

I created the table first and then I tried adding the constraing by running this

ALTER TABLE "Patient"
ADD [CONSTRAINT "DoctorForeign"] FOREIGN KEY ("DoctorID") REFERENCES "Doctor" ("DoctorID");

and got this error

1: Unexpected token: [ in statement [ALTER TABLE "Patient"
ADD ]

could you please tell me if I am missing something or if something is wrong?

Thank you

[Villeroy]

Unexpected token: [
means that the bracket is unexpected. On that documentation page, brackets mark alternative options. They are not meant to be literal.

However, I missed your point. You don't want to add a constraint. You want to add a foreign key.
This works directly when creating a table:

Code: Select all

CREATE TABLE "Patient" (
  "PatientID" int NOT NULL ,
  "Name" varchar(100) NOT NULL,
  "Phone" varchar(15) NULL,
  "Email" varchar(30) NULL,
  "Address" varchar(150) NOT NULL,
  "AddedDate" date NULL,
  "DoctorID" int,
  PRIMARY KEY ("PatientID"),
  FOREIGN KEY ("DoctorID") REFERENCES "Doctor" ("DoctorID")
)

Or if the table already exists:

Code: Select all

ALTER TABLE "Patient" ADD FOREIGN KEY "DoctorID" REFERENCES "Doctor" ("DoctorID")

A constraint looks like this:

Code: Select all

ALTER TABLE "Patient" ADD CONSTRAINT "No_Future" CHECK ("Date" <= CURRENT_DATE)

and yes, it is possible to add a constraint during table creation (I was not aware):

CREATE [MEMORY | CACHED | [GLOBAL] TEMPORARY | TEMP | TEXT] TABLE <name>
( <columnDefinition> [, ...] [, <constraintDefinition>...] )
[ON COMMIT {DELETE | PRESERVE} ROWS];

CREATE TABLE <name> ( ) are mandatory.
WIthin the braces there can be a chain of column definitions and constraint definitions.
A constraint definition looks like this:

constraintDefinition

[CONSTRAINT <name>]
UNIQUE ( <column> [,<column>...] ) |
PRIMARY KEY ( <column> [,<column>...] ) |
FOREIGN KEY ( <column> [,<column>...] )
REFERENCES <refTable> ( <column> [,<column>...])
[ON {DELETE | UPDATE}
{CASCADE | SET DEFAULT | SET NULL}][2] |
CHECK(<search condition>)

You can declare combinations of columns as unique, as PK, as FK and your an add a CHECK constraint.