CREATE TABLE Examples
1. Create the dept table where the name column must be unique:
CREATE TABLE dept (
name CHAR(10) NOT NULL,
location CHAR(20),
CONSTRAINT dept_unique UNIQUE(name));
Create the emp_const table with a referential constraint to the name column in table dept:
CREATE TABLE emp_const (
name CHAR(10) NOT NULL,
salary DECIMAL(10,2),
dept CHAR(10)
CONSTRAINT empref REFERENCES dept(name));
2. Create a table in default location:
CREATE TABLE region_orders (
partno VARCHAR(10),
cust_no INTEGER NOT NULL,
qty INTEGER,
unit_price FLOAT,
orderno INTEGER NOT NULL);
INSERT INTO region_orders VALUES('123-45',101,10,10.0,1001),('123-45', 202, 100, 10.00, 1002),('543-21',987,2,99.99,1007),('543-21',654,33,99.99,1004),('987-65', 321, 20, 29.99, 1003);
3. Create a table in location1:
Note: Database "test" was extended to the new location with this command:
extenddb -llocation1 -a/data/location1 -Udata test
CREATE TABLE orders AS SELECT orderno, partno, ANSIDATE('2013-12-31') odate, cust_no
FROM region_orders WITH LOCATION = (location1);
UPDATE orders SET odate = '2014-01-01' WHERE orderno = 1001;
4. Create a table with columns in separate locations (vertically partitioned):
Note: Database test was extended to location2 with this command:
extenddb -llocation2 -a/data/location2 -Udata test
CREATE TABLE store (
store_country CHAR(10) NOT NULL,
store_city CHAR(10) NOT NULL)
WITH LOCATION(store_country)=(location1), LOCATION(store_city)=(location2);
5. Create a table with columns in multiple locations (horizontally partitioned):
CREATE TABLE store (
store_country CHAR(10) NOT NULL,
store_region CHAR(10) NOT NULL,
store_city CHAR(10) NOT NULL) WITH
LOCATION(store_country)=(location1,location2),
LOCATION(store_region)=(location3,location4),
LOCATION(store_city)=(location3, location4);
6. Create the customer table. Explicitly declare the structure to be X100:
CREATE TABLE customer (
customer_no INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(40) NOT NULL,
customer_status_code CHAR(10) NOT NULL)
WITH STRUCTURE = X100;
7. Create the customer table. Explicitly declare the structure to be a standard Ingres table:
CREATE TABLE customer (
customer_no INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(40) NOT NULL,
customer_status_code CHAR(10) NOT NULL)
WITH STRUCTURE = HEAP;
8. Create a table, add a table comment, and provide access to it using GRANT:
CREATE TABLE tbint (rnum INTEGER NOT NULL, cbint BIGINT);
COMMENT ON TABLE tbint IS 'This describes table TBINT.';
GRANT SELECT ON TABLE tbint TO PUBLIC;
9. Create a structure of related tables:
CREATE TABLE tcons1 (
rnum INTEGER NOT NULL,
col1 INTEGER NOT NULL,
CONSTRAINT tcons1pk PRIMARY KEY (col1));
GRANT SELECT ON TABLE tcons1 TO PUBLIC;
CREATE TABLE tcons2 (
rnum INTEGER NOT NULL,
col2 INTEGER NOT NULL, col3 INTEGER NOT NULL,
CONSTRAINT tcons2pk PRIMARY KEY (col2,col3));
GRANT SELECT ON TABLE tcons2 TO PUBLIC;
CREATE TABLE tcons3 (rnum INTEGER NOT NULL,
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
col4 INTEGER,
CONSTRAINT tcons3pk PRIMARY KEY (col1,col2),
CONSTRAINT tconsfk1 FOREIGN KEY (col1) REFERENCES tcons1,
CONSTRAINT tcons3fk2 FOREIGN KEY (col1,col2) REFERENCES tcons2 (col2,col3),
CONSTRAINT tconsuk UNIQUE (col3),
CONSTRAINT tcons2fk3 FOREIGN KEY (col4,col3)
REFERENCES tcons2 (col3,col2));
GRANT SELECT ON TABLE tcons3 TO PUBLIC;
CREATE TABLE tcons4 (
rnum INTEGER NOT NULL,
col1 INTEGER NOT NULL, col2 INTEGER,
CONSTRAINT tcons4pk PRIMARY KEY (col1),
CONSTRAINT tcons4fk FOREIGN KEY (col2) REFERENCES tcons4 (col1))
WITH STRUCTURE = HEAP;
10. Define a table with a BY DEFAULT identity column that maps to a negative incrementing sequence. INSERT statements can override the sequence by explicitly defining values for column d1, but in the absence of an explicit value, the INSERT will generate the next value from the underlying sequence.
CREATE TABLE t2 (
d1 DECIMAL(15) GENERATED BY DEFAULT AS IDENTITY (START WITH -1 INCREMENT BY -10),
d2 FLOAT, . . .);
11. Create a partitioned table with rows distributed evenly based on a hash value:
CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER),
emp_rating INTEGER)
WITH JOURNALING,
PARTITION = (HASH ON emp_no
16 PARTITIONS WITH LOCATION = (location1));
12. Create two partitions in db_loc1:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH on a 2 PARTITIONS);
13. Create two partitions with location db_loc2. Location db_loc1 will be ignored:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH on a 2 PARTITIONS WITH LOCATION=(db_loc2));
14. Create four partitions, two in location db_loc2, one in location db_loc1, and one in location db_loc3:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH on a 2 PARTITIONS WITH LOCATION=(db_loc2),
1 PARTITION,
1 PARTITION WITH LOCATION=(db_loc3));
15. Create a table in which the social security number is encrypted using AES 128-bit encryption. Do not add 16 bytes of random bits to the field to further obfuscate the encrypted value (NOSALT):
CREATE TABLE socsectab (
fname CHAR(10),
lname CHAR(20),
socsec CHAR(11) ENCRYPT NOSALT )
WITH ENCRYPTION=AES128, PASSPHRASE='this is a secret';
16. Create a table in which data for column c2, which contains salary data, is encrypted using AES 256-bit encryption. Salt is added to the field by default:
CREATE TABLE t1 (
c1 CHAR(20) NOT NULL,
c2 MONEY ENCRYPT)
WITH ENCRYPTION=AES256, PASSPHRASE='decoder ring';
17. Create a table with a sampled min-max index on two columns:
CREATE TABLE sales_fact (
sales_date ANSIDATE,
value INTEGER2,
quantity FLOAT8)
WITH MINMAX=(sales_date, quantity), MINMAX_SAMPLES;
18. Create a table with the address and salary columns masked:
CREATE TABLE employee(
name VARCHAR(20),
address VARCHAR(20) MASKED,
salary FLOAT MASKED AS 0);
19. Create a partitioned table with rows distributed automatically (that is, randomly):
CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER,
emp_rating INTEGER)
WITH JOURNALING,
PARTITION = (AUTOMATIC 8 PARTITIONS WITH LOCATION = (location1));