So I'm finishing up an assignment for a database design class and I have an sql question.

I'm making 6 seperate tables, with at least one primary key(in some cases 2) and one sometimes two foreign keys.

When I try to assign the Foreign key references, I get an error stating: ERROR at line 15:
ORA-00942: table or view does not exist

I know this is because the referring table hasnt been created yet, since its the fourth or fifth table that is to be created.

Is it possible to "force" the sql to create the table even without the referring table having been created yet, or do I need to add AMEND TABLE <table name> after all the tables have been created?

Thanks!

Just as an FYI, here is my code:

CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR(1) ,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30) ,
SEX CHAR(1),
SALARY DECIMAL(10,2),
SUPER_SSN CHAR(9),
DNO INT NOT NULL,
PRIMARY KEY (SSN)
FOREIGN KEY (SUPER_SSN) REFERENCES EMPLOYEE (SSN),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER)
);

I get the error at the REFERENCES DEPARTMENT line because the Department table has not yet been created. After looking at my work, the DEPARTMENT table is the next table to be created.

The usual process is to create all the tables first, then alter them with any necessary triggers, constraints, etc after they're all created. There may be a way to force the command to ignore dependencies in your particular SQL variant, but it's probably not a good general practice.

I don't think Oracle, or any SQL implementation I know of, will let you have a non-existent column as a foreign key, even if you have the IGNORE ERRORS flag set.

I would generally define all the tables first, then execute the necessary ALTER TABLE statements to reference them. It may be possible to wrap the whole thing in a block and force it to parse the entire thing as a single action but I doubt it.

Can the order of the table definitions be rearranged so that there are no forward references?

If not, it might be a pain to insert the data too.

Excellent, thanks for the help gang!!

I will just do the Amend Table after all the tables are created.

Pal - Hadnt thought of that, I will check to see if I can reorder the tables, but I'm thinking I wont be able to.

Are you building an HR system? I hope you also are implementing audit trails, including logging changes to the table data. You may also want to make address a text/blob type. Other information you might want to record is employee username, contact numbers, email address, check printing name, hiring/termination date (although you may want to put this in a separate table like Microsoft Dynamics GP does in anticipation of rehiring), pay grade/system (e.g, full time or part time), and not to mention immigration/citizenship, deceased/alive, and other vital data.

We use Microsoft Dynamics GP on Terminal Server. It's expensive at $2,500/user (we have 15 users), but it's really one of the better ERP/accounting systems out there. To bypass the user limit, we can create limited user accounts which have specific access to certain profiles and use the SOAP API to build our internal web interface to execute business rules and ensure data integrity. In general, it's a bad idea to modify data directly on a complicated ERP system such as SAGE, Microsoft, PeopleSoft, or Oracle. You will want to use their web services or middleware (Cobra) APIs to insert/modify/destroy the data you need.

So I'm finishing up an assignment for a database design class and I have an sql question.

S/he's going through the generic sort of payroll design system you get in all those classes that enjoy forcing you to view data modeling like an exercise in implementing Orwellian Dystopia. Kinda doubt s/he's going to blow several thousand dollars to get a double-A++ good :>

If this is just a class assignment, you can use an "implied" foreign key, where you name the field the same as what it will be in another table (I do that just so I remember what table it should be from), and every time its referenced, its holding a value that is gotten from the other table.

At that point though, you're breaking referential integrity (is that what its called? I forget) if you delete the data associated with the foreign keys. If its a supposed to be a 'serious' type of application (financial, etc) then you probably shouldn't do it that way.