How can I create a sequential SQL creation script for Oracle?

Question

Something like:

create table Employee(
ID int primary key,
Name nvarchar(200)
IDArea int foreign key references Area(ID)
);
go
create table Area(
ID int primary key,
Name nvarchar(200)
);

Does something like this exist in Oracle?

Answer 1

Yes, just leave out the "GO" keywords and put the statements in a file:

create table Area(
ID int primary key,
Name nvarchar2(200)
);
create table Employee(
ID int primary key,
Name nvarchar2(200),
IDArea int references Area(ID)
);

You must create the Area primary key before the foreign key that references it, so I have swapped these around. Also the foreign key syntax is slightly different in Oracle.

• Sergio Tapia: This code would work? I thought foreign keys were different in Oracle from Microsoft SQL.
• Tony Andrews: The syntax is fine almost - I have just corrected the FK syntax for Oracle in my answer. You need to create the constraints in the parent/child order too - you can't reference Area(ID) before the Area table exists.
• Tony Andrews: Oh, and I changed nvarchar to nvarchar2 to make it work in Oracle. But VARCHAR2 (without the N) is the more usual string datatype in Oracle.

Answer 2

You should first create the master table, forget nvarchar datatype, and eventually the script would be :

create table Area( 
ID number primary key, 
Name varchar2(200) 
); 
create table Employee( 
ID number primary key, 
Name varchar2(200) ,
IDArea number, constraint fk_idarea foreign key (idarea) references Area(ID) 
);