CREATE Statement
This page shows you how to use the CREATE
statement. The CREATE
statement is a DDL statement. It lets you create tables, sequence, views, object, and such based on the implementation of the database.
Oracle
You can use the CREATE
statement to create objects, like tables, views, objects, sequences, types, functions, and procedures. These examples demonstrate how to define tables, sequences, and views.
CREATE TABLE
Statement
The following demonstrates creating a table with inline PRIMARY KEY
and NOT NULL
constraints on columns. The CONSTRAINT constraint_name
is an optional phrase in the statement. It is a best practice to provide meaningful constraint names because they show up in error messages when DML statements attempt to violate the constraint. When you leave the phrase out of the statement, the system generated constraint name is displayed. System generated constraint names can be researched in the database catalog but it slows problem resolution time.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE military_user ( military_user_id NUMBER CONSTRAINT pk1_military_user PRIMARY KEY , military_user_name VARCHAR2(20) CONSTRAINT nn1_military_user NOT NULL , military_user_group_id NUMBER CONSTRAINT nn2_military_user NOT NULL , military_user_branch NUMBER CONSTRAINT nn3_military_user NOT NULL , military_user_rank NUMBER CONSTRAINT nn4_military_user NOT NULL , last_name VARCHAR2(20) , first_name VARCHAR2(20) , middle_initial VARCHAR2(1) , created_by NUMBER CONSTRAINT nn5_military_user NOT NULL , creation_date DATE CONSTRAINT nn6_military_user NOT NULL , last_updated_by NUMBER CONSTRAINT nn7_military_user NOT NULL , last_update_date DATE CONSTRAINT nn8_military_user NOT NULL); |
CREATE TABLE military_user ( military_user_id NUMBER CONSTRAINT pk1_military_user PRIMARY KEY , military_user_name VARCHAR2(20) CONSTRAINT nn1_military_user NOT NULL , military_user_group_id NUMBER CONSTRAINT nn2_military_user NOT NULL , military_user_branch NUMBER CONSTRAINT nn3_military_user NOT NULL , military_user_rank NUMBER CONSTRAINT nn4_military_user NOT NULL , last_name VARCHAR2(20) , first_name VARCHAR2(20) , middle_initial VARCHAR2(1) , created_by NUMBER CONSTRAINT nn5_military_user NOT NULL , creation_date DATE CONSTRAINT nn6_military_user NOT NULL , last_updated_by NUMBER CONSTRAINT nn7_military_user NOT NULL , last_update_date DATE CONSTRAINT nn8_military_user NOT NULL);
The following demonstrates an inline PRIMARY KEY
constraint, several inline NOT NULL
constraints, four DEFAULT
values, an out-of-line UNIQUE
constraint, and two out-of-line FOREIGN KEY
constraints.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TABLE military_lookup ( military_lookup_id NUMBER CONSTRAINT pk1_military_lookup PRIMARY KEY , table_name VARCHAR2(30) CONSTRAINT nn1_military_lookup NOT NULL , column_name VARCHAR2(30) CONSTRAINT nn2_military_lookup NOT NULL , lookup_type VARCHAR2(30) CONSTRAINT nn3_military_lookup NOT NULL , lookup_value VARCHAR2(30) CONSTRAINT nn4_military_lookup NOT NULL , lookup_meaning VARCHAR2(80) CONSTRAINT nn5_military_lookup NOT NULL , lookup_code VARCHAR2(10) , created_by NUMBER DEFAULT -1 , creation_date DATE DEFAULT SYSDATE , last_updated_by NUMBER DEFAULT -1 , last_update_date DATE DEFAULT SYSDATE , CONSTRAINT un1_military_lookup UNIQUE (table_name, column_name, lookup_type, lookup_value, lookup_code) , CONSTRAINT fk1_military_lookup FOREIGN KEY(created_by) REFERENCES military_user(military_user_id) , CONSTRAINT fk2_military_lookup FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id)); |
CREATE TABLE military_lookup ( military_lookup_id NUMBER CONSTRAINT pk1_military_lookup PRIMARY KEY , table_name VARCHAR2(30) CONSTRAINT nn1_military_lookup NOT NULL , column_name VARCHAR2(30) CONSTRAINT nn2_military_lookup NOT NULL , lookup_type VARCHAR2(30) CONSTRAINT nn3_military_lookup NOT NULL , lookup_value VARCHAR2(30) CONSTRAINT nn4_military_lookup NOT NULL , lookup_meaning VARCHAR2(80) CONSTRAINT nn5_military_lookup NOT NULL , lookup_code VARCHAR2(10) , created_by NUMBER DEFAULT -1 , creation_date DATE DEFAULT SYSDATE , last_updated_by NUMBER DEFAULT -1 , last_update_date DATE DEFAULT SYSDATE , CONSTRAINT un1_military_lookup UNIQUE (table_name, column_name, lookup_type, lookup_value, lookup_code) , CONSTRAINT fk1_military_lookup FOREIGN KEY(created_by) REFERENCES military_user(military_user_id) , CONSTRAINT fk2_military_lookup FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id));
The FOREIGN KEY
phrase references a column name found in the table within the parentheses. The REFERENCES
phrase points to the table name followed by a column name in that table in parentheses. It is important to note that the table name may be the same table or a different table. When the referenced table is the same table the FOREIGN KEY
constrains the relationship between one column or set of columns and another column or set of columns in the same or different row of the table. This type of relationship is known as a recursive or self-referencing relationship.
It’s also very important to note that FOREIGN KEY
constraints don’t force you to provide a value when inserting or updating rows. You must use an inline NOT NULL constraint and an out-of-line FOREIGN KEY
constraint when you want the foreign key constrained column to be a mandatory column.
It is possible to create an optional foreign key constrained column. Let’s say you’re modeling an on military base Pizza Parlor’s ordering system. A customer may call in for delivery, which requires a home address; or for pickup, which doesn’t require a home address. You can create a FOREIGN KEY
constraint on the ADDRESS_ID
column of the ADDRESS
table with the following inline syntax that creates a TELEPHONE
table:
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE military_telephone ( telephone_id NUMBER CONSTRAINT pk1_military_lookup PRIMARY KEY ... , address_id NUMBER REFERENCES address(address_id) ... , created_by NUMBER DEFAULT -1 , creation_date DATE DEFAULT SYSDATE , last_updated_by NUMBER DEFAULT -1 , last_update_date DATE DEFAULT SYSDATE , CONSTRAINT fk1_military_lookup FOREIGN KEY(created_by) REFERENCES military_user(military_user_id) , CONSTRAINT fk2_military_lookup FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id)); |
CREATE TABLE military_telephone ( telephone_id NUMBER CONSTRAINT pk1_military_lookup PRIMARY KEY ... , address_id NUMBER REFERENCES address(address_id) ... , created_by NUMBER DEFAULT -1 , creation_date DATE DEFAULT SYSDATE , last_updated_by NUMBER DEFAULT -1 , last_update_date DATE DEFAULT SYSDATE , CONSTRAINT fk1_military_lookup FOREIGN KEY(created_by) REFERENCES military_user(military_user_id) , CONSTRAINT fk2_military_lookup FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id));
CREATE INDEX
Statement
Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT
, UPDATE
, MERGE
, and DELETE
statements. Indexes are also called fast access paths.
The Oracle Database 12c database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve SQL statement performance. The downside is that they impose overhead on every INSERT
, UPDATE
, MERGE
, and DELETE
statement, because the database maintains them by inserting, updating, or deleting items for each related change in the tables that the indexes support.
Indexes have two key properties—usability and visibility. Indexes are both usable and visible by default. That means they are visible to the Oracle Database 12c cost-based optimizer and usable when statements run against the tables they support.
You have the ability to make any index invisible, in which case queries and DML statements won’t use the index because they won’t see it. However, the cost-based optimizer still sees the index and maintains it with any DML statement change. That means making an index invisible isn’t quite like making the index unusable or like dropping it temporarily. An invisible index becomes overhead and thus is typically a short-term solution to run a resource- intensive statement that behaves better without the index while avoiding the cost of rebuilding it after the statement runs.
It is also possible to make an index unusable, in which case it stops collecting information and becomes obsolete and the database drops its index segment. You rebuild the index when you change it back to a usable index.
Indexes work on the principal of a key. A key is typically a set of columns or expressions on which you can build an index, but it’s possible that a key can be a single column. An index based on a set of columns is a composite, or concatenated, index.
Indexes can be unique or non-unique. You create a unique index anytime you constrain a column by assigning a primary key or unique constraint, but they’re indirect indexes. You create a direct unique index on a single column with the following syntax against two non-unique columns:
1 2 | CREATE INDEX military_lookup_u1 ON military_lookup ( lookup_table ); |
CREATE INDEX military_lookup_u1 ON military_lookup ( lookup_table );
You could convert this to a non-unique index on two columns by using this syntax:
1 2 | CREATE INDEX military_lookup_u1 ON military_lookup ( lookup_table, lookup_column ); |
CREATE INDEX military_lookup_u1 ON military_lookup ( lookup_table, lookup_column );
Making the index unique is straightforward;, you only need to add a UNIQUE
key wordk to the CREATE INDEX
statement, like
1 2 3 4 | CREATE UNIQUE INDEX military_lookup_u1 ON military_lookup ( lookup_table , lookup_column , lookup_type ); |
CREATE UNIQUE INDEX military_lookup_u1 ON military_lookup ( lookup_table , lookup_column , lookup_type );
Most indexes use a B-tree (balanced tree). A B-tree is composed of three types of blocks—a root branch block for searching next-level blocks, branch blocks for searching other branch blocks, or and leaf blocks that store pointers to row values. B-trees are balanced because all leaf-blocks are at the same level, which means the length of search is the same to any element in the tree. All branch blocks store the minimum key prefix required to make branching decisions through the B-tree.
CREATE SEQUENCE
Statement
Oracle requires that you explicitly define sequences, which differs from MySQL and SQL Server where they’re implicitly created with the table. The following demonstrates creating an explicit sequence in Oracle with a default START WITH
value of 1
.
1 | CREATE SEQUENCE military_user_s1; |
CREATE SEQUENCE military_user_s1;
The following demonstrates creating an explicit sequence in Oracle with an override START WITH
value of 1001
.
1 | CREATE SEQUENCE military_user_s1 START WITH 1001; |
CREATE SEQUENCE military_user_s1 START WITH 1001;
CREATE VIEW
Statement
The Oracle database also let you define views. A view is the result of a SQL query or SELECT
statement. You have two options with views. The most common is a query only view, which is like the example. The less frequent view is a key preserved view. You can update one or more of the tables in this type of view, provided the respective restrictions of the database engine are met.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE OR REPLACE VIEW military_personnel AS SELECT ml2.lookup_meaning||' ' || first_name||DECODE(middle_initial,'',' ',' '||middle_initial||'. ')||last_name || ', '||ml1.lookup_meaning AS military_person FROM military_user mu , military_lookup ml1 , military_lookup ml2 WHERE military_user_branch = ml1.military_lookup_id AND ml1.table_name = 'MILITARY_USER' AND ml1.column_name = 'MILITARY_BRANCH' AND military_user_rank = ml2.military_lookup_id AND ml2.table_name = 'MILITARY_USER' AND ml2.column_name = 'MILITARY_RANK'; |
CREATE OR REPLACE VIEW military_personnel AS SELECT ml2.lookup_meaning||' ' || first_name||DECODE(middle_initial,'',' ',' '||middle_initial||'. ')||last_name || ', '||ml1.lookup_meaning AS military_person FROM military_user mu , military_lookup ml1 , military_lookup ml2 WHERE military_user_branch = ml1.military_lookup_id AND ml1.table_name = 'MILITARY_USER' AND ml1.column_name = 'MILITARY_BRANCH' AND military_user_rank = ml2.military_lookup_id AND ml2.table_name = 'MILITARY_USER' AND ml2.column_name = 'MILITARY_RANK';
Oracle supports a CREATE OR REPLACE
DDL syntax for views. This syntax lets you avoid a DROP
statement before creating a view again.
MySQL
You can use the CREATE
statement to create tables, views, synonyms, stored functions and procedures. A key difference between Oracle and MySQL is that sequences are implicitly created with tables by using the AUTO_INCREMENT
keyword. These examples demonstrate how to define tables and views.
CREATE INDEX
Statement
The following demonstrates how you create an INDEX
. You should note that table must already exist to run this command.
1 2 | CREATE INDEX military_user_key ON military_user (military_user_name, military_user_group_id, military_user_branch, military_user_rank, last_name, first_name, middle_initial); |
CREATE INDEX military_user_key ON military_user (military_user_name, military_user_group_id, military_user_branch, military_user_rank, last_name, first_name, middle_initial);
CREATE TABLE
Statement
The following demonstrates creating a table with inline PRIMARY KEY
and NOT NULL
constraints on columns. The CONSTRAINT constraint_name
is not an optional phrase for an inline PRIMARY KEY
or NOT NULL
constraint in a CREATE
statement. While it is a best practice to provide meaningful constraint names because they show up in error messages when DML statements attempt to violate the constraint. You must do so by defining the PRIMARY KEY
constraint out-of-line.
When you leave the phrase out of the statement, the system generated constraint name is displayed. System generated constraint names can be researched in the database catalog but it slows problem resolution time.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE IF NOT EXISTS military_user ( military_user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , military_user_name VARCHAR(20) NOT NULL , military_user_group_id INT UNSIGNED NOT NULL , military_user_branch INT UNSIGNED NOT NULL , military_user_rank INT UNSIGNED NOT NULL , last_name VARCHAR(20) , first_name VARCHAR(20) , middle_initial VARCHAR(1) , created_by INT UNSIGNED NOT NULL , creation_date DATE NOT NULL , last_updated_by INT UNSIGNED NOT NULL , last_update_date DATE NOT NULL) ENGINE=InnoDB; |
CREATE TABLE IF NOT EXISTS military_user ( military_user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , military_user_name VARCHAR(20) NOT NULL , military_user_group_id INT UNSIGNED NOT NULL , military_user_branch INT UNSIGNED NOT NULL , military_user_rank INT UNSIGNED NOT NULL , last_name VARCHAR(20) , first_name VARCHAR(20) , middle_initial VARCHAR(1) , created_by INT UNSIGNED NOT NULL , creation_date DATE NOT NULL , last_updated_by INT UNSIGNED NOT NULL , last_update_date DATE NOT NULL) ENGINE=InnoDB;
The IF NOT EXISTS
clause tells the database to check to see if the table is already defined in the database catalog before running the CREATE
statement. This prevents a CREATE
table from raising a 1050
error because the table is already defined in the database.
ERROR 1050 (42S01): Table 'sample' already exists |
ERROR 1050 (42S01): Table 'sample' already exists
You give a meaningful name to a PRIMARY KEY
constraint by using an out-of-line constraint definition in a CREATE
statement. The following shows you the comparative syntax.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE IF NOT EXISTS military_user ( military_user_id INT UNSIGNED AUTO_INCREMENT , military_user_name VARCHAR(20) NOT NULL , military_user_group_id INT UNSIGNED NOT NULL , military_user_branch INT UNSIGNED NOT NULL , military_user_rank INT UNSIGNED NOT NULL , last_name VARCHAR(20) , first_name VARCHAR(20) , middle_initial VARCHAR(1) , created_by INT UNSIGNED NOT NULL , creation_date DATE NOT NULL , last_updated_by INT UNSIGNED NOT NULL , last_update_date DATE NOT NULL , CONSTRAINT military_user_pk PRIMARY KEY(military_user_id)) ENGINE=InnoDB; |
CREATE TABLE IF NOT EXISTS military_user ( military_user_id INT UNSIGNED AUTO_INCREMENT , military_user_name VARCHAR(20) NOT NULL , military_user_group_id INT UNSIGNED NOT NULL , military_user_branch INT UNSIGNED NOT NULL , military_user_rank INT UNSIGNED NOT NULL , last_name VARCHAR(20) , first_name VARCHAR(20) , middle_initial VARCHAR(1) , created_by INT UNSIGNED NOT NULL , creation_date DATE NOT NULL , last_updated_by INT UNSIGNED NOT NULL , last_update_date DATE NOT NULL , CONSTRAINT military_user_pk PRIMARY KEY(military_user_id)) ENGINE=InnoDB;
The following demonstrates an inline PRIMARY KEY
constraint, several inline NOT NULL
constraints, four DEFAULT
values, an out-of-line UNIQUE
constraint, and two out-of-line FOREIGN KEY
constraints. The FOREIGN KEY
constraints require that you designate the table in the scope of a specific database engine that supports referential integrity. The InnoDB is generally the more frequent engine, but you could also do this with other engines.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT 'CREATE TABLE military_lookup' AS "Statement"; CREATE TABLE IF NOT EXISTS military_lookup ( military_lookup_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , table_name VARCHAR(30) NOT NULL , column_name VARCHAR(30) NOT NULL , lookup_type VARCHAR(30) NOT NULL , lookup_value VARCHAR(30) NOT NULL , lookup_meaning VARCHAR(80) NOT NULL , lookup_code VARCHAR(10) , created_by INT UNSIGNED DEFAULT 0 , creation_date DATE , last_updated_by INT UNSIGNED DEFAULT 0 , last_update_date DATE , CONSTRAINT un1_military_lookup UNIQUE INDEX (table_name, column_name, lookup_type, lookup_value, lookup_code) , CONSTRAINT fk1_military_lookup FOREIGN KEY(created_by) REFERENCES military_user(military_user_id) , CONSTRAINT fk2_military_lookup FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id)) ENGINE=InnoDB; |
SELECT 'CREATE TABLE military_lookup' AS "Statement"; CREATE TABLE IF NOT EXISTS military_lookup ( military_lookup_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , table_name VARCHAR(30) NOT NULL , column_name VARCHAR(30) NOT NULL , lookup_type VARCHAR(30) NOT NULL , lookup_value VARCHAR(30) NOT NULL , lookup_meaning VARCHAR(80) NOT NULL , lookup_code VARCHAR(10) , created_by INT UNSIGNED DEFAULT 0 , creation_date DATE , last_updated_by INT UNSIGNED DEFAULT 0 , last_update_date DATE , CONSTRAINT un1_military_lookup UNIQUE INDEX (table_name, column_name, lookup_type, lookup_value, lookup_code) , CONSTRAINT fk1_military_lookup FOREIGN KEY(created_by) REFERENCES military_user(military_user_id) , CONSTRAINT fk2_military_lookup FOREIGN KEY(last_updated_by) REFERENCES military_user(military_user_id)) ENGINE=InnoDB;
The FOREIGN KEY
phrase references a column name found in the table within the parentheses. The REFERENCES
phrase points to the table name followed by a column name in that table in parentheses. It is important to note that the table name may be the same table or a different table. When the referenced table is the same table the FOREIGN KEY
constrains the relationship between one column or set of columns and another column or set of columns in the same or different row of the table. This type of relationship is known as a recursive or self-referencing relationship.
It’s also very important to note that FOREIGN KEY
constraints don’t force you to provide a value when inserting or updating rows. You must use an inline NOT NULL constraint and an out-of-line FOREIGN KEY
constraint when you want the foreign key constrained column to be a mandatory column.
It is possible to create an optional foreign key constrained column. Let’s say you’re modeling an on military base Pizza Parlor’s ordering system. A customer may call in for delivery, which requires a home address; or for pickup, which doesn’t require a home address. You can create a FOREIGN KEY
constraint on the ADDRESS_ID
column of the ADDRESS
table with only an out-of-line foreign key constraint syntax.
CREATE INDEX
Statement
Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT
, UPDATE
, REPLACE INTO
, and DELETE
statements. Indexes are also called fast access paths.
In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve SQL statement performance. The downside is that they impose overhead on every INSERT
, UPDATE
, REPLACE INTO
, and DELETE
statement, because the database maintains them by inserting, updating, or deleting items for each related change in the tables that the indexes support.
Indexes have two key properties—usability and visibility. Indexes are both usable and visible by default. That means they are visible to the MySQL cost-based optimizer and usable when statements run against the tables they support.
You have the ability to make any index invisible, in which case queries and DML statements won’t use the index because they won’t see it. However, the cost-based optimizer still sees the index and maintains it with any DML statement change. That means making an index invisible isn’t quite like making the index unusable or like dropping it temporarily. An invisible index becomes overhead and thus is typically a short-term solution to run a resource-intensive statement that behaves better without the index while avoiding the cost of rebuilding it after the statement runs.
It is also possible to make an index unusable, in which case it stops collecting information and becomes obsolete and the database drops its index segment. You rebuild the index when you change it back to a usable index.
Indexes work on the principal of a key. A key is typically a set of columns or expressions on which you can build an index, but it’s possible that a key can be a single column. An index based on a set of columns is a composite, or concatenated, index.
Indexes can be unique or non-unique. You create a unique index anytime you constrain a column by assigning a primary key or unique constraint, but they’re indirect indexes. You create a direct unique index on a single column with the following syntax against two non-unique columns:
1 2 | CREATE INDEX military_lookup_u1 ON military_lookup ( military_lookup ); |
CREATE INDEX military_lookup_u1 ON military_lookup ( military_lookup );
You could convert this to a non-unique index on two columns by using this syntax:
1 2 | CREATE INDEX military_lookup_u1 ON military_lookup ( lookup_table, lookup_column ); |
CREATE INDEX military_lookup_u1 ON military_lookup ( lookup_table, lookup_column );
Making the index unique is straightforward;, you only need to add a UNIQUE
key wordk to the CREATE INDEX
statement, like
1 2 3 4 | CREATE UNIQUE INDEX military_lookup_u1 ON military_lookup ( lookup_table , lookup_column , lookup_type ); |
CREATE UNIQUE INDEX military_lookup_u1 ON military_lookup ( lookup_table , lookup_column , lookup_type );
Most indexes use a B-tree (balanced tree). A B-tree is composed of three types of blocks—a root branch block for searching next-level blocks, branch blocks for searching other branch blocks, or and leaf blocks that store pointers to row values. B-trees are balanced because all leaf-blocks are at the same level, which means the length of search is the same to any element in the tree. All branch blocks store the minimum key prefix required to make branching decisions through the B-tree.
CREATE SEQUENCE
Statement
MySQL creates sequences implicitly when you use AUTO_INCREMENT
in the CREATE TABLE
syntax. Therefore, there is no syntax to let you create a sequence manually.
CREATE VIEW
Statement
The MySQL database also let you define views. A view is the result of a SQL query or SELECT
statement. You have two options with views. The most common is a query only view, which is like the example. The less frequent view is a key preserved view. You can update one or more of the tables in this type of view, provided the respective restrictions of the database engine our met.
1 2 3 4 5 6 7 8 9 10 11 | CREATE VIEW military_personnel AS SELECT CONCAT(ml2.lookup_meaning,' ',first_name,IF(middle_initial IS NULL,' ',CONCAT(' ',middle_initial,'. ')),last_name,', ',ml1.lookup_meaning) AS military_person FROM military_user mu , military_lookup ml1 , military_lookup ml2 WHERE military_user_branch = ml1.military_lookup_id AND ml1.table_name = 'MILITARY_USER' AND ml1.column_name = 'MILITARY_BRANCH' AND military_user_rank = ml2.military_lookup_id AND ml2.table_name = 'MILITARY_USER' AND ml2.column_name = 'MILITARY_RANK'; |
CREATE VIEW military_personnel AS SELECT CONCAT(ml2.lookup_meaning,' ',first_name,IF(middle_initial IS NULL,' ',CONCAT(' ',middle_initial,'. ')),last_name,', ',ml1.lookup_meaning) AS military_person FROM military_user mu , military_lookup ml1 , military_lookup ml2 WHERE military_user_branch = ml1.military_lookup_id AND ml1.table_name = 'MILITARY_USER' AND ml1.column_name = 'MILITARY_BRANCH' AND military_user_rank = ml2.military_lookup_id AND ml2.table_name = 'MILITARY_USER' AND ml2.column_name = 'MILITARY_RANK';
Unfortunately, MySQL doesn’t support a CREATE OR REPLACE
clause in their implementation of the DDL syntax that creates views. That means you have to DROP
a view before creating it anew.
SQL Server
Written by michaelmclaughlin
September 22nd, 2009 at 11:31 pm
Posted in
10 Responses to 'CREATE Statement'
Subscribe to comments with RSS or TrackBack to 'CREATE Statement'.
In your code for CREATE TABLE military_user the constraint name ‘nn4_military_user’ is used twice. Curiously, the military_lookup table doesn’t use nn4_ at all, it jumps from nn3_ to nn5_.
Banyan
8 Mar 10 at 11:57 am
Thanks for pointing that out. Sometimes editing changes introduce errors. They’re fixed and tonight I’ll post the working code for both Oracle and MySQL.
michaelmclaughlin
8 Mar 10 at 1:40 pm
Just a simple grammar mistake in section CREATE VIEW Statement, the phrase “provided the respective restrictions of the database engine our met” should be “are met” instead of “our met”.
Thanks!
wuzuprad
16 Sep 10 at 2:02 pm
Thanks. Fixed above.
michaelmclaughlin
16 Sep 10 at 3:11 pm
Can tables be created with out-of-line NOT NULL constraints. If naming constraints is a best practice, this would be useful.
Cory Larcade
25 Sep 13 at 11:13 am
You repeat the word “sequences” two times in,
“You can use the CREATE statement to create objects, like tables, views, objects, sequences, types, functions, procedures, and sequences.”
Martin
11 Jan 14 at 1:55 pm
Martin, Thanks, it’s fixed.
michaelmclaughlin
22 Oct 14 at 2:41 am
Cory, You can’t create out-of-line
NOT NULL
constraints in Oracle, but you can use theALTER
statement to change a column from null allowed toNOT NULL
or vice-versa. MySQL treatsNOT NULL
constraints as column properties and therefore can’t let you create them asNOT NULL
constraints with an out-of-line syntax. The best practice in Oracle is to name your constraints.michaelmclaughlin
22 Oct 14 at 3:05 am
How do you
CREATE
anINDEX
?Peter Decker
16 Jan 15 at 2:25 pm
Peter, Check the added text for Chapter 6 on page 160 of the Oracle Database 11g and MySQL 5.6. It covers how to create an index. Thanks.
michaelmclaughlin
21 Jan 15 at 1:00 am