Hypersonic DB question

[Joseph Sweet]

Trying to follow a tutorial here

Tutorial

Running this script in order to create tables:

    
 
CREATE TABLE STOREACCESS(USERNAME VARCHAR(15),PASSWORD VARCHAR(15),USERID VARCHAR(6) NOT NULL PRIMARY KEY,CONSTRAINT SYS_STOREACCESS_UQ UNIQUE(USERNAME))
 
CREATE TABLE CUSTOMER(CUSTOMERID VARCHAR(6) NOT NULL PRIMARY KEY,USERID VARCHAR(6),FIRSTNAME VARCHAR(15),LASTNAME VARCHAR(15),ADDRESS VARCHAR(25),PHONE VARCHAR(10),MESSAGE VARCHAR(25),SHAREHOLDER_STAT VARCHAR(1) NOT NULL,CONSTRAINT SYS_FK_CUSTOMER FOREIGN KEY(USERID) REFERENCES STOREACCESS(USERID))
 
CREATE TABLE SUPPLIER(SUPPLIERID VARCHAR(6) NOT NULL PRIMARY KEY,USERID VARCHAR(6),FIRSTNAME VARCHAR(15),LASTNAME VARCHAR(15),ADDRESS VARCHAR(25),MESSAGE VARCHAR(25),CREDIT_LIMIT DECIMAL(12,2) NOT NULL,CONSTRAINT SYS_FK_SUPPLIER FOREIGN KEY(USERID) REFERENCES STOREACCESS(USERID))
 
CREATE TABLE MANAGER(MANAGERID VARCHAR(6) NOT NULL PRIMARY KEY,USERID VARCHAR(6),FIRSTNAME VARCHAR(15),LASTNAME VARCHAR(15),ADDRESS VARCHAR(25),MESSAGE VARCHAR(25),SALARY DECIMAL(12,2) NOT NULL,CONSTRAINT SYS_FK_MANAGER FOREIGN KEY(USERID) REFERENCES STOREACCESS(USERID))
 
CREATE TABLE ITEM(ITEMID VARCHAR(6) NOT NULL PRIMARY KEY,SUPPLIERID VARCHAR(6),DESCRIPTION VARCHAR(20),QUANTITY INTEGER,PRICE DECIMAL(12,2) NOT NULL,CONSTRAINT SYS_FK_SUPPLIER FOREIGN KEY(SUPPLIERID) REFERENCES SUPPLIER(SUPPLIERID))
 

However, the ITEM table is not created. It keeps telling me that

    
 
Constraint already exist: SYS_FK_SUPPLIER in statement [CREATE TABLE ITEM(ITEMID VARCHAR(6) NOT NULL PRIMARY KEY,SUPPLIERID VARCHAR(6),DESCRIPTION VARCHAR(20),QUANTITY INTEGER,PRICE DECIMAL(12,2) NOT NULL,CONSTRAINT SYS_FK_SUPPLIER FOREIGN KEY(SUPPLIERID) REFERENCES SUPPLIER(SUPPLIERID))] /Erroe code: -60/ State: S0011
 

What is the meaning of this error? What do I need to change to make it work???

Thanks, I am quite new to this.

[Jan Cumps]

You need to provide a unique name for your constraint.

You name your constraint SYS_FK_SUPPLIER for table SUPPLIER,
and you also try to create a constraint with the same name for table ITEM.

Try someting like ... CONSTRAINT <TABLENAME>_FK_SUPPLIER.

Regards, Jan

[Joseph Sweet]

Thanks. What does CONSTRAINT mean?

I could not find anything about it on Google.

[Jan Cumps]

A constraint is a validation mechanism.
It will guard that the data that you write adheres to your rules.

Your constraint CONSTRAINT SYS_STOREACCESS_UQ UNIQUE(USERNAME) will allow only unique values in column USERNAME.
If your table STOREACCESS has a record with USERNAME == 'Joseph Sweet', and you try to insert a new record with USERNAME == 'Joseph Sweet', it will fail.
Also updating another record to set USERNAME == 'Joseph Sweet' will fail.

Your constraint guarantees that USERNAME will only have unique values. No duplicates are allowed.

Regards, Jan