forked from oracle-samples/oracle-db-examples
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcreateDBObjects.sql
More file actions
49 lines (39 loc) · 1.22 KB
/
createDBObjects.sql
File metadata and controls
49 lines (39 loc) · 1.22 KB
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
# code Sample from the tutorial at https://learncodeshare.net/2015/06/02/basic-crud-operations-using-cx_oracle/
# The following creates and populates the tables used for the tutorial
# These scripts use identity columns available in Oracle 12.1. If you are using an older version you will need
# to add your own trigger/sequence functionality to generate the id values.
CREATE TABLE lcs_people (
id NUMBER GENERATED BY DEFAULT AS identity,
name VARCHAR2(20),
age NUMBER,
notes VARCHAR2(100)
)
/
ALTER TABLE LCS_PEOPLE
ADD CONSTRAINT PK_LCS_PEOPLE PRIMARY KEY ("ID")
/
CREATE TABLE LCS_PETS (
id NUMBER GENERATED BY DEFAULT AS IDENTITY,
name VARCHAR2(20),
owner NUMBER,
type VARCHAR2(100)
)
/
ALTER TABLE LCS_PETS ADD CONSTRAINT PK_LCS_PETS PRIMARY KEY ("ID")
/
ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
/
INSERT INTO lcs_people (name, age, notes)
VALUES ('Bob', 35, 'I like dogs')
/
INSERT INTO lcs_people (name, age, notes)
VALUES ('Kim', 27, 'I like birds')
/
INSERT INTO lcs_pets (name, owner, type)
VALUES ('Duke', 1, 'dog')
/
INSERT INTO lcs_pets (name, owner, type)
VALUES ('Pepe', 2, 'bird')
/
COMMIT
/