Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Trying to Create tables

I'm trying to create these tables but for some reason the SC_CAR table gets an error saying "table or view doesn't exist". The other 2 table are perfectly fine to make and have no problems. I've remade them and tried to organize them in a different way but that doesn't work. Does anyone know what is wrong with the table?
create table SC_CAR(
CARSERIAL CHAR(8)
, CUSTNAME VARCHAR(20)
, make VARCHAR(10) NOT NULL
, model CHAR(2) NOT NULL
, caryear CHAR(4) NOT NULL
, extcolour VARCHAR(15) NOT NULL
, trim VARCHAR(15) NOT NULL
, enginetype VARCHAR (10) NOT NULL
, baselistprice DECIMAL(10,2) NOT NULL
, PURCHINVNO CHAR(6)
, purchcost DECIMAL(10,2)
, CONSTRAINT pkcar PRIMARY KEY(CARSERIAL)
, CONSTRAINT fkcar1 FOREIGN KEY(CUSTNAME) REFERENCES SC_CUST(CUSTNAME)
, CONSTRAINT fkcar2 FOREIGN KEY(PURCHINVNO) REFERENCES SC_PURCHINV(PURCHINVNO));
create table SC_CUST(
CUSTNAME VARCHAR(20)
, custaddress VARCHAR(30) NOT NULL
, custcity VARCHAR(15) NOT NULL
, custprovince VARCHAR(10) NOT NULL
, custpostal CHAR(6) NOT NULL
, custhomephone CHAR(10) NOT NULL
, custworkphone CHAR(10)
, CONSTRAINT pkcust PRIMARY KEY(CUSTNAME));
create table SC_PURCHINV(
PURCHINVNO CHAR(6)
, purchasedfrom VARCHAR(20) NOT NULL
, purchdate DATE NOT NULL
, CONSTRAINT pkpurchinv PRIMARY KEY(PURCHINVNO));
Answers
-
From a very quick look: are you trying to create the tables in the order you show them? If so, then the error is clear: you have foreign key references in the first table, pointing to columns in the other tables. If you try to create the first table before the other tables, that won't work - since those other tables don't exist yet, so you can't reference them in your foreign key constraints. This seems consistent with the error message you got.
The solution is easy, too: create the second and third table first, and THEN create what is the "first" table in your listing.
-
Hi,
I can create all 3 tables without any problem.
You need to create sc_car last, of course. Why did you post the script with sc_car first?
By the way:
2fddb624-7170-40d0-8686-8962507bdc12 wrote:..., CONSTRAINT fkcar1 FOREIGN KEY(CUSTNAME) REFERENCES SC_CUST(CUSTNAME), CONSTRAINT fkcar2 FOREIGN KEY(PURCHINVNO) REFERENCES SC_PURCHINV(PURCHINVNO));create table SC_CUST(CUSTNAME VARCHAR(20), custaddress VARCHAR(30) NOT NULL, custcity VARCHAR(15) NOT NULL, custprovince VARCHAR(10) NOT NULL, custpostal CHAR(6) NOT NULL, custhomephone CHAR(10) NOT NULL, custworkphone CHAR(10), CONSTRAINT pkcust PRIMARY KEY(CUSTNAME));...
Foreign keys should always be values that you'll never want to change, but it looks like you're using the only name that a customer has as a foreign key. That will cause you extra work if a customer ever changes his name, or if you discover that you entered a customer name wrong.
A lot of guys use Surrogate Keys for primary and foreign keys. These are usually numbers, generated by sequences, that have no meaning except to uniquely identify entities.
The CHAR data type is nothing but trouble. Use VARCHAR2 for strings.
-
Please do as below