How is it possible to convert the following oracle sql to a create table to mysql
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY' NLS_DATE_LANGUAGE='english'; select sysdate from dual;
CREATE TABLE dept (
value1 DATE not null,
value2 NUMBER(21,6) not null,
value3 NUMBER(21,6),
value4 references tem(temno));
Example of insert data:
insert into emp(value1,value2,value3,value4) values('02-APR-1989',26850,20,30);
First replace Oracle's NUMBER datatype with MySql's DECIMAL datatype.
Your table definition references another table named tem here:
value4 references tem(temno)
this is so called inline foreign key constraint
You cannot create table dept if table tem has not been previously created, so the whole script in Oracle must look like this:
CREATE TABLE tem(
temno number primary key
);
INSERT INTO tem VALUES ( 1 );
CREATE TABLE dept (
value1 DATE not null,
value2 number(21,6) not null,
value3 number(21,6),
value4 references tem(temno)
);
INSERT INTO dept VALUES( date '2017-01-22', 22.0, 13.5, 1 );
You have also to create table tem in MySql before creating the table dept
MySql doesn't support inline foreign key constraints, only out of line foreign key constrains work, so the tables definition in MySql must look like this:
CREATE TABLE tem(
temno decimal primary key
);
INSERT INTO tem VALUES ( 1 );
CREATE TABLE dept (
value1 DATE not null,
value2 decimal(21,6) not null,
value3 decimal(21,6),
value4 decimal,
constraint foreign key (value4) references tem(temno)
);
INSERT INTO dept VALUES( date '2017-01-22', 22.0, 13.5, 1 );
Live demo: http://sqlfiddle.com/#!9/173b2/1
Note: a datatype of value4 decimal must be the same as a dataypepe of temno decimal in table tem, and also tem(temno) column must be a primary key, otherwise you will get an error.
emp- > " Employee " . And You are asking aboutdept-> "Derpartment" . And its classic schema example from Oraclevalue2 INT not null, value3 INT, FOREIGN KEY (value4) REFERENCES tem(temno)STR_TO_DATE('2013-02-11', '%d-%b-%Y')and as 1st argument Your date-string. Or JustDATE(...)