Link to home
Start Free TrialLog in
Avatar of huzefaq
huzefaq

asked on

need to create some sql server DDL queries Oracle database compatible

I am migrating tables from sql server to oracle. Need help in converting SQl server DDL queries Oracle compatible. Any help will be graetly appreciated
CREATE TABLE ARTIST(
	ArtistID 		Int 		NOT NULL IDENTITY (1,1),
	Name 			Char(25) 	NOT NULL,
	Nationality 		Char(30) 	NULL,
	Birthdate 		Numeric (4,0) 	NULL,
	DeceasedDate 		Numeric (4,0)	NULL,
 
	CONSTRAINT 	ArtistPK 	PRIMARY KEY (ArtistID),
	CONSTRAINT 	ArtistAK1 	UNIQUE (Name),
 
	CONSTRAINT 	NationalityValues 	CHECK
				(Nationality IN ('Canadian', 'English', 'French', 'German',
				'Mexican', 'Russian', 'Spanish', 'US')),
	CONSTRAINT 	BirthValuesCheck 	CHECK
				(Birthdate < DeceasedDate),
	CONSTRAINT 	ValidBirthYear 		CHECK
				(Birthdate LIKE '[1-2][0-9][0-9][0-9]'),
	CONSTRAINT 	ValidDeathYear 		CHECK
				(DeceasedDate LIKE '[1-2][0-9][0-9][0-9]')
	);
 
CREATE TABLE WORK(
	WorkID 			Int 		NOT NULL IDENTITY (500,1),
	Title 			Char(25) 	NOT NULL,
	Description 		Varchar(1000) 	NULL DEFAULT 'Unknown provenance',
	Copy 			Char(8) 	NOT NULL,
	ArtistID 		Int 		NOT NULL,
 
	CONSTRAINT 	WorkPK 		PRIMARY KEY (WorkID),
	CONSTRAINT 	WorkAK1 	UNIQUE (Title, Copy),
 
	CONSTRAINT 	ArtistFK
			FOREIGN KEY(ArtistID) REFERENCES ARTIST (ArtistID)
				ON DELETE NO ACTION
				ON UPDATE NO ACTION
	);
 
 
CREATE TABLE CUSTOMER(
	CustomerID 		Int 		NOT NULL IDENTITY (1000,1),
	Name 			Char(25) 	NOT NULL,
	Street 			Char(30) 	NULL,
	City			Char(35) 	NULL,
	State			Char(2) 	NULL,
	ZipPostalCode 		Char(9) 	NULL,
	Country 		Char(50) 	NULL,
	AreaCode 		Char(3) 	NULL,
	PhoneNumber 		Char(8) 	NULL,
	Email 			Char(100) 	NULL,
 
	CONSTRAINT 	CustomerPK 	PRIMARY KEY (CustomerID),
	CONSTRAINT 	CustomerAK1 	UNIQUE (Name)
	);
 
 
CREATE TABLE TRANS(
	TransactionID 		Int 		NOT NULL IDENTITY (100,10),
	DateAcquired 		DateTime	NOT NULL,
	AcquisitionPrice 	Numeric (8,2) 	NULL,
	PurchaseDate 		DateTime	NULL,
	SalesPrice 		Numeric (8,2) 	NULL,
	AskingPrice 		Numeric (8,2) 	NULL,
	CustomerID 		Int 		NULL,
	WorkID 			Int 		NOT NULL,
 
	CONSTRAINT 	TransactionPK 	PRIMARY KEY (TransactionID),
 
	CONSTRAINT 	TransactionWorkFK
			FOREIGN KEY(WorkID) REFERENCES WORK (WorkID)
				ON DELETE NO ACTION
				ON UPDATE NO ACTION,
 
	CONSTRAINT 	TransactionCustomerFK 
			FOREIGN KEY(CustomerID) REFERENCES CUSTOMER (CustomerID)
				ON UPDATE NO ACTION
				ON DELETE NO ACTION,
 
	CONSTRAINT 	SalesPriceRange 	CHECK
				((SalesPrice > 1000) AND (SalesPrice <=200000)),
	CONSTRAINT 	ValidTransDate 		CHECK
				(DateAcquired <= PurchaseDate)
	);
 
 
CREATE TABLE CUSTOMER_ARTIST_INT(
	ArtistID 		Int 		NOT NULL,
	CustomerID 		Int 		NOT NULL,
 
	CONSTRAINT 	CustomerArtistPK PRIMARY KEY (ArtistID, CustomerID),
 
	CONSTRAINT 	Customer_Artist_Int_ArtistFK
			FOREIGN KEY (ArtistID) REFERENCES ARTIST (ArtistID)
				ON UPDATE NO ACTION
				ON DELETE CASCADE,
	CONSTRAINT 	Customer_Artist_Int_CustomerFK
			FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID)
				ON UPDATE CASCADE
				ON DELETE CASCADE
	);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sujith
Sujith
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
if you have more than these 2 tables.
download install oracle sql delveloper
follow the directions..  
http://www.oracle.com/technology/tech/migration/workbench/index_sqldev_omwb.html
Avatar of huzefaq
huzefaq

ASKER

I have only these two tables. So beside identity is there anything else which I need to change. Also what is the identity function?

Thanks
identity is the same as using a sequence to get the nextval.
it just a column to make the row "unique"