Trying to Create tables

2fddb624-7170-40d0-8686-8962507bdc12

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));

mathguy

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.

Frank Kulash

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.

John Thorton

Please do as below

Video tutorial how to change nickname available