table creaction error

[tezeey]

hello there..

Im having a problem with this table, when i try running it, it gives the error shown bellow, i am using sql developer, I have tried others ways for writting the constrains but its just not working.

Create table borrowed_equipments(equipment_name constraint for_key_equipment_name foreign key references equipments(equipment_name),
user_name constraint fk_user_name foreign key references users(user_name),date_taken date,return_date date);



ora-02253:constraint specified is not allowed here

 

[Thargy]

CREATE TABLE BORROWED_EQUIPMENTS
(
EQUIPMENT_NAME VARCHAR2(50),
USER_NAME      VARCHAR2(50),
DATE_BORROWED  DATE,
DATE_RETURNED  DATE
);

ALTER TABLE BORROWED_EQUIPMENTS
ADD CONSTRAINT FK_EQUIPMENT_NAME
FOREIGN KEY (EQUIPMENT_NAME)
REFERENCES EQUIPMENTS(EQUIPMENT_NAME);

ALTER TABLE BORROWED_EQUIPMENTS
ADD CONSTRAINT FK_EQUIPMENT_USERS
FOREIGN KEY (USER_NAME)
REFERENCES USERS(USER_NAME);

ALTER TABLE BORROWED_EQUIPMENTS
ADD CONSTRAINT NN_EQUPMENT_NAME
CHECK (EQUIPMENT_NAME IS NOT NULL);

ALTER TABLE BORROWED_EQUIPMENTS
ADD CONSTRAINT NN_USER_NAME
CHECK (USER_NAME IS NOT NULL);

ALTER TABLE BORROWED_EQUIPMENTS
ADD CONSTRAINT ONLY_RETURN_BORROWED_EQUIPMENT
CHECK (NOT ((DATE_RETURNED IS NOT NULL) AND (DATE_BORROWED IS NULL)));

ALTER TABLE BORROWED_EQUIPMENTS
ADD CONSTRAINT CANT_RETURN_BEFORE_BORROWING
CHECK (DATE_BORROWED <= DATE_RETURNED);

Note the integrity constraints to prevent unknown people from borrowing equipment and returning it before they borrowed it.

Without the create table statements for the user table,I have had to guess that their primary keys are VARCHAR2(50), although I personally prefer to use an integer USER_ID as primary key.

It is generally bad form to use lower case for these statements.

Regards

T

 

[Beilstwh]

to find out the layout of a table you have access, in sql plus simply type DESC TABLE_NAME



Bill
Lead Application Developer
New York State, USA