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_F K 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_F K REFERENCES books(isbn),
lib_card_FK NUMBER CONSTRAINT customer_lib_ca rd_FK REFERENCES customer(lib_ca rd_id),
employ_id_FK NUMBER CONSTRAINT employee_employ _id_FK REFERENCES employee(employ ee_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),
);
4 2273
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.
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_F K 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_F K REFERENCES books(isbn),
lib_card_FK NUMBER CONSTRAINT customer_lib_ca rd_FK REFERENCES customer(lib_ca rd_id),
employ_id_FK NUMBER CONSTRAINT employee_employ _id_FK REFERENCES employee(employ ee_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.
Nevermind I just figured it out I had a mispelling on references and also a duplicate FOREIGN KEY CONSTRAINT NAME.
Thanks Anyways
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Kamil |
last post by:
Hello
What should I use for better perfomance since
unique constraint always use index ?
Thanks
Kamil
|
by: adammitchell |
last post by:
How can you indicate that a FOREIGN KEY constraint references two
columns in two different tables?
"SQL Server Books Online" show an example of how to reference two
columns in the SAME table:
REFERENCES ref_table )
Here is the error and the 'bad' SQL code:
Server: Msg 8148, Level 16, State 1, Line 4
|
by: Sudip Chakraborty |
last post by:
Is there a way to see constraint validation errors while loading xml into a
DataSet ? I'm interested in the line number in the xml file which is causing
the error. I've enclosed the relevant stack trace below.
at System.Data.DataSet.FailedEnableConstraints()
at System.Data.DataSet.EnableConstraints()
at System.Data.DataSet.set_EnforceConstraints(Boolean value)
|
by: Bradley |
last post by:
Has anyone else noticed this problem? I converted the back-end to A2000
and the performance problem was fixed. We supply a 97 and 2000 version
of our software so we kept the backend in A97 to make upgrading simple
for users. We've done it like that for years but a new client has been
having severe performance issues... solved by converting the backend to
2000.
--
regards,
Bradley
|
by: Megan |
last post by:
Hi Everybody-
I know that this is a really, really long post, but I wanted to try to
give you as much background as possible.
So here's a quick overview of the issues I'm asking for help with:
1.) I'm trying to create a many to many relationship, and I get the
following Error when I try to enforce referential integrity.
| |
by: Beacher |
last post by:
Hello,
I'm trying to create a relationship between two tables, a one to many
tblDeal being the primary table, I'm trying to create a relationship
between tblDeal and tblCondition .. i set tbldeal as the primary table,
select DealID and select DealID from tblCondition, but when it goes to
save it I get this error:
'tblDeal (chrisb)' table saved successfully
'tblCondition (chrisb)' table
|
by: ferg |
last post by:
I have a Customer table. The table has two different CHECK constraints.
Then there is the Customer details dialog, which provides the user with
an UI for changing users. I have some UPDATE sql, which is called once
the user clicks the OK button on this dialog.
try
{
int rows = cmd.ExecuteNonQuery();
}
catch(SqlException se)
|
by: Laurence |
last post by:
Hi there,
How to differentiate between unique constraint and unique index? These
are very similar but I cannot differentiate them?
Could someone give me a hand?
Thanks in advance
|
by: rorajoey |
last post by:
Violation of UNIQUE KEY constraint 'IX_surveyQuestions'. Cannot insert duplicate key in object 'dbo.surveyQuestions'.
This might seem like a simple matter of trying to insert a row with ID=20 when there's already one with that ID, but the problem is a bit more complicated. The table is supposed to auto-increment the value for the primary key when a new record is inserted. But no matter what I do, I can't seem to insert more than one record...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |