98 lines
2.4 KiB
MySQL
98 lines
2.4 KiB
MySQL
drop table R1 purge;
|
|
create table R1 as
|
|
select
|
|
rownum ID,
|
|
round(dbms_random.value(1,100000)) N1,
|
|
round(dbms_random.value(1,1000)) N2,
|
|
case
|
|
when mod(rownum,10)=0 then 'BINGO'
|
|
else
|
|
dbms_random.string('X',5)
|
|
end S1,
|
|
TO_DATE(
|
|
TRUNC(
|
|
dbms_random.value(TO_CHAR(DATE '1960-01-01','J')
|
|
,TO_CHAR(DATE '2011-12-31','J')
|
|
)
|
|
),'J'
|
|
) D1,
|
|
'Record #'||to_char(rownum) S2
|
|
from
|
|
dual
|
|
connect by level<=100000;
|
|
|
|
|
|
drop table R2 purge;
|
|
create table R2 as
|
|
select
|
|
rownum ID,
|
|
dbms_random.string('X',5) S1,
|
|
case
|
|
when mod(rownum,3)=0 then 'M3'
|
|
else
|
|
dbms_random.string('X',5)
|
|
end S2,
|
|
round(dbms_random.value(1,100)) N1,
|
|
TO_DATE(
|
|
TRUNC(
|
|
dbms_random.value(TO_CHAR(DATE '1960-01-01','J')
|
|
,TO_CHAR(DATE '2011-12-31','J')
|
|
)
|
|
),'J'
|
|
) D1
|
|
from
|
|
dual
|
|
connect by level<=1000;
|
|
|
|
drop table R3 purge;
|
|
create table R3 as
|
|
select
|
|
rownum ID,
|
|
dbms_random.string('X',5) S1,
|
|
case
|
|
when mod(rownum,2)=0 then 'M2'
|
|
else
|
|
dbms_random.string('X',5)
|
|
end S2,
|
|
round(dbms_random.value(1,1000)) N1
|
|
from
|
|
dual
|
|
connect by level<=1000;
|
|
|
|
|
|
drop table R4 purge;
|
|
create table R4 as
|
|
select
|
|
rownum ID,
|
|
dbms_random.string('X',5) S1,
|
|
TO_DATE(
|
|
TRUNC(
|
|
dbms_random.value(TO_CHAR(DATE '1960-01-01','J')
|
|
,TO_CHAR(DATE '2011-12-31','J')
|
|
)
|
|
),'J'
|
|
) D1
|
|
from
|
|
dual
|
|
connect by level<=10000;
|
|
|
|
|
|
|
|
ALTER TABLE R1 MODIFY (ID NOT NULL);
|
|
ALTER TABLE R1 ADD CONSTRAINT R1_PK PRIMARY KEY (ID) ENABLE;
|
|
|
|
ALTER TABLE R2 MODIFY (ID NOT NULL);
|
|
ALTER TABLE R2 ADD CONSTRAINT R2_PK PRIMARY KEY (ID) ENABLE;
|
|
|
|
ALTER TABLE R3 MODIFY (ID NOT NULL);
|
|
ALTER TABLE R3 ADD CONSTRAINT R3_PK PRIMARY KEY (ID) ENABLE;
|
|
|
|
ALTER TABLE R4 MODIFY (ID NOT NULL);
|
|
ALTER TABLE R4 ADD CONSTRAINT R4_PK PRIMARY KEY (ID) ENABLE;
|
|
|
|
|
|
ALTER TABLE R1 ADD CONSTRAINT R1_FK1 FOREIGN KEY(N2) REFERENCES R2(ID) ENABLE;
|
|
ALTER TABLE R3 ADD CONSTRAINT R3_FK1 FOREIGN KEY(N1) REFERENCES R2(ID) ENABLE;
|
|
ALTER TABLE R2 ADD CONSTRAINT R2_FK1 FOREIGN KEY(N1) REFERENCES R4(ID) ENABLE;
|
|
|