constraint error when creating table relationships

[antpal]

I am not sure exactly what i am doing run but when I run this code in sql plus i get constraint error on most of my tables except author and books table. Please help I am new to Programming in PL/SQL.



--Create All Tables

CREATE TABLE authors (
auth_id NUMBER(7),
f_name VARCHAR2(30),
l_name VARCHAR2(50),
auth_specialty VARCHAR2(30),
CONSTRAINT auth_id_pk PRIMARY KEY(auth_id)
);


CREATE TABLE books (
isbn CHAR (10) PRIMARY KEY,
title VARCHAR2 (50),
qty_owned NUMBER(3),
qty_on_hand NUMBER(3),
cost NUMBER,
location VARCHAR2(35),
auth_FK NUMBER CONSTRAINT books_auth_FK REFERENCES authors(auth_id)
);

CREATE TABLE book_details(
Num_Pages NUMBER(10),
copyright DATE,
publisher VARCHAR2(50),
category VARCHAR2(20),
isbn_nu_FK CHAR CONSTRAINT books_isbn_nu_FK REFERNECES books(isbn)
);

CREATE TABLE customer(
lib_card_id NUMBER(9) PRIMARY KEY, --no duplicates
f_name VARCHAR2(30),
l_name VARCHAR2(50),
num_str VARCHAR2(30),
city VARCHAR2(30),
zip VARCHAR2(7),
phone VARCHAR2(13),
card_start_date DATE,
card_exp_date DATE,
member_since DATE,
rental_id_FK NUMBER CONSTRAINT customer_rental_id_FK REFERENCES rentals(rental_id)
);

CREATE TABLE rental_details(
rental_id_FK NUMBER CONSTRAINT rentals_rental_id_FK REFERENCES rentals(rental_id),
isbn_num_FK NUMBER CONSTRAINT books_isbn_num_FK REFERENCES books(isbn),
quanity NUMBER(5),
rental_date DATE,
due_date DATE,
);

CREATE TABLE rentals(
rental_id NUMBER(7) PRIMARY KEY,
isbn_nu_FK NUMBER CONSTRAINT books_isbn_nu_FK REFERENCES books(isbn),
lib_card_FK NUMBER CONSTRAINT customer_lib_card_FK REFERENCES customer(lib_card_id),
employ_id_FK NUMBER CONSTRAINT employee_employ_id_FK REFERENCES employee(employee_id)
);

CREATE TABLE employee(
employee_id NUMBER(9) PRIMARY KEY,
f_name VARCHAR2(30),
l_name VARCHAR2(50),
num_str VARCHAR2(35),
city VARCHAR2(25),
state VARCHAR2(2),
zip VARCHAR2(5),
SSN VARCHAR2(9),
multi_lingual VARCHAR2(20),
ext VARCHAR2(3),
position VARCHAR2(25),
);

[debasisdas]

please check your code again.

some of your tables are dependent on each other like the customer and rentals table.so when you create the child table it will search for master table for estabnlishing the relationship, which is yet to be created .
to avoid this problem first create the tables then add the realtionships separately.
hope that helps you. if u stil have doubts please do post back.

[antpal]

please check your code again.

some of your tables are dependent on each other like the customer and rentals table.so when you create the chile table it will search for master table for estabnlishing the relationship, which is yet to be created .
to avoid this problem first create the tables then add the realtionships separately.
hope that helps you. if u stil have doubts please do post back.

Hi I fixed some code and rearranged my tables to the following:
--Create All Tables

CREATE TABLE authors (
auth_id NUMBER(7) PRIMARY KEY,
f_name VARCHAR2(30),
l_name VARCHAR2(50),
auth_specialty VARCHAR2(30)
);


CREATE TABLE books (
isbn CHAR (10) PRIMARY KEY,
title VARCHAR2 (50),
qty_owned NUMBER(3),
qty_on_hand NUMBER(3),
cost NUMBER,
location VARCHAR2(35),
auth_FK NUMBER CONSTRAINT books_auth_FK REFERENCES authors(auth_id)
);

CREATE TABLE book_details(
Num_Pages NUMBER(10),
copyright DATE,
publisher VARCHAR2(50),
category VARCHAR2(20),
isbn_nu_FK CHAR CONSTRAINT books_isbn_nu_FK REFERNECES books(isbn)
);

CREATE TABLE customer(
lib_card_id NUMBER(9) PRIMARY KEY, --no duplicates
f_name VARCHAR2(30),
l_name VARCHAR2(50),
num_str VARCHAR2(30),
city VARCHAR2(30),
zip VARCHAR2(7),
phone VARCHAR2(13),
card_start_date DATE,
card_exp_date DATE,
member_since DATE
);

CREATE TABLE rentals(
rental_id NUMBER(7) PRIMARY KEY,
isbn_nu_FK CHAR CONSTRAINT books_isbn_nu_FK REFERENCES books(isbn),
lib_card_FK NUMBER CONSTRAINT customer_lib_card_FK REFERENCES customer(lib_card_id),
employ_id_FK NUMBER CONSTRAINT employee_employ_id_FK REFERENCES employee(employee_id)
);

CREATE TABLE employee(
employee_id NUMBER(9) PRIMARY KEY,
f_name VARCHAR2(30),
l_name VARCHAR2(50),
num_str VARCHAR2(35),
city VARCHAR2(25),
state VARCHAR2(2),
zip VARCHAR2(5),
SSN VARCHAR2(9),
multi_lingual VARCHAR2(20),
ext VARCHAR2(3),
position VARCHAR2(25)
);

CREATE TABLE rental_details(
rental_id_FK NUMBER CONSTRAINT rentals_rental_id_FK REFERENCES rentals(rental_id),
isbn_num_FK CHAR CONSTRAINT books_isbn_num_FK REFERENCES books(isbn),
quanity NUMBER(5),
rental_date DATE,
due_date DATE
);


the only constraint problem I am having now is in book_details table for my foreign key constraint isbn_nu_FK. this is commingup as error and not sure why. If you could shed some light on this I'd greatly appreciate it. Thanks also for the Help before.

[antpal]

Nevermind I just figured it out I had a mispelling on references and also a duplicate FOREIGN KEY CONSTRAINT NAME.

Thanks Anyways

[babind]

hi this is anjan from hyd i saw ur programmes but i noticed some problem in that one
that is


CREATE TABLE books (
isbn CHAR (10) PRIMARY KEY,
title VARCHAR2 (50),
qty_owned NUMBER(3),
qty_on_hand NUMBER(3),
cost NUMBER,
location VARCHAR2(35),
auth_FK NUMBER CONSTRAINT books_auth_FK REFERENCES authors(auth_id)
);


CREATE TABLE rental_details(
rental_id_FK NUMBER CONSTRAINT rentals_rental_id_FK REFERENCES rentals(rental_id),
isbn_num_FK NUMBER CONSTRAINT books_isbn_num_FK REFERENCES books(isbn),
quanity NUMBER(5),
rental_date DATE,
due_date DATE,
);

in books table isbn column data type is char its ok
but rental_details table isbn_num datatype is num how can it takes char into num

first u mention both column datatype is same either char or number then try
i hope this time not get any error if u any error plz send me that error
i will try once again
but i hope this time not get any error
first u check all column datatype are match whos given references names of that then rectify erros


bye.........!

Nevermind I just figured it out I had a mispelling on references and also a duplicate FOREIGN KEY CONSTRAINT NAME.

Thanks Anyways