FilesExpand file tree

 master

/

example_create_table.sql

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

121 lines (103 loc) · 3.08 KB

 master

/

example_create_table.sql

Copy path

Top

File metadata and controls

• Code
• Blame

121 lines (103 loc) · 3.08 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

-- +----------------------------------------------------------------------------+

-- | Jeffrey M. Hunter |

-- | jhunter@idevelopment.info |

-- | www.idevelopment.info |

-- |----------------------------------------------------------------------------|

-- | Copyright (c) 1998-2015 Jeffrey M. Hunter. All rights reserved. |

-- |----------------------------------------------------------------------------|

-- | DATABASE : Oracle |

-- | FILE : example_create_table.sql |

-- | CLASS : Examples |

-- | PURPOSE : Simple create table script. |

-- | NOTE : As with any code, ensure to test this script in a development |

-- | environment before attempting to run it in production. |

-- +----------------------------------------------------------------------------+

/*

* --------------------------------------------------------

* ---------------- CREATE TABLE (DEPT) -------------------

* --------------------------------------------------------

*/

prompt Dropping Table (dept)...

DROP TABLE dept CASCADE CONSTRAINTS

/

prompt Creating Table (dept)...

CREATE TABLE dept (

deptno NUMBER(2)

, dname VARCHAR2(14)

, loc VARCHAR2(13)

)

TABLESPACE users

STORAGE (

INITIAL 128K

NEXT 128K

MINEXTENTS 1

MAXEXTENTS 121

PCTINCREASE 0

)

/

ALTER TABLE dept

ADD CONSTRAINT dept_pk PRIMARY KEY(deptno)

USING INDEX

TABLESPACE idx

STORAGE (

INITIAL 64K

NEXT 64K

MINEXTENTS 1

MAXEXTENTS 121

PCTINCREASE 0

)

/

ALTER TABLE dept

MODIFY ( dname CONSTRAINT dept_nn1 NOT NULL

)

/

/*

* -------------------------------------------------------

* ---------------- CREATE TABLE (EMP) -------------------

* -------------------------------------------------------

*/

prompt Dropping Table (emp)...

DROP TABLE emp CASCADE CONSTRAINTS

/

prompt Creating Table (emp)...

CREATE TABLE emp (

empno NUMBER(4)

, ename VARCHAR2(10)

, job VARCHAR2(9)

, mgr NUMBER(4)

, hiredate DATE

, sal NUMBER(7,2)

, comm NUMBER(7,2)

, deptno NUMBER(2)

)

TABLESPACE users

STORAGE (

INITIAL 128K

NEXT 128K

MINEXTENTS 1

MAXEXTENTS 121

PCTINCREASE 0

)

/

ALTER TABLE emp

ADD CONSTRAINT emp_pk PRIMARY KEY(empno)

USING INDEX

TABLESPACE idx

STORAGE (

INITIAL 64K

NEXT 64K

MINEXTENTS 1

MAXEXTENTS 121

PCTINCREASE 0

)

/

ALTER TABLE emp

MODIFY ( ename CONSTRAINT emp_nn1 NOT NULL

, job CONSTRAINT emp_nn2 NOT NULL

, hiredate CONSTRAINT emp_nn3 NOT NULL

)

/

ALTER TABLE emp

ADD CONSTRAINT emp_fk1 FOREIGN KEY (deptno)

REFERENCES dept(deptno)

/