Таблицы oracle

Содержание

Syntax for SQL Server

The following SQL statement defines the «Personid» column to be an auto-increment primary key field in the «Persons» table:


CREATE TABLE Persons (     Personid int IDENTITY(1,1) PRIMARY KEY,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Age int );

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the «Personid» column should start at value 10 and increment by 5, change it to IDENTITY(10,5).

To insert a new record into the «Persons» table, we will NOT have to specify a value for the «Personid» column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName) VALUES (‘Lars’,’Monsen’);

The SQL statement above would insert a new record into the «Persons» table. The «Personid» column would be assigned a unique value. The «FirstName» column would be set to «Lars» and the «LastName» column would be set to «Monsen».

Oracle private temporary table examples

Let’s take some examples of using the statement.

1) Creating a private temporary table example which is transaction-specific

First, create a new temporary table that is transaction-specific:

Next, insert a row into the table:

Then, view the contents of the table:

After that, commit the transaction:

Finally, query data from the table:

Oracle issued the following error because it already dropped the table at the end of the transaction.

2) Creating a private temporary table which is session-specific

First, create a new temporary table that is session specific:

Next, insert a row into the table:

Then, view data of the table:

After that, commit the transaction and check contents of the table:

Finally, reconnect and view the contents of the table:

Oracle issued the following error because it removed the  table at the end of the session.

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

  • NOT NULL — Ensures that a column cannot have a NULL value
  • UNIQUE — Ensures that all values in a column are different
  • PRIMARY KEY — A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
  • FOREIGN KEY — Uniquely identifies a row/record in another table
  • CHECK — Ensures that all values in a column satisfies a specific condition
  • DEFAULT — Sets a default value for a column when no value is specified
  • INDEX — Used to create and retrieve data from the database very quickly

Create Table Foreign Key Syntax

Like the primary key definition, you can declare a foreign key both inline and out of line in the SQL Create Table command.

Inline foreign keys are declared like this:

You use the word REFERENCES, then the name of the table that the foreign key refers to, then within brackets you specify the column that the foreign key links to.

The other method of adding a foreign key using the CREATE TABLE command is the out of line method.

You need to start with the word CONSTRAINT, then the name of the foreign key. The name needs to be unique across the database, so I like to start with the term “fk”, then the two tables I am referring to.

Then, you specify the words FOREIGN KEY, then the name of the other table within brackets. Then you add the word REFERENCES, then within brackets, you specify the column name from the table you’re referring to (which is probably the primary key).

Introduction to Oracle private temporary tables

If you are familiar with other database products such as SQL Server, PostgreSQL, and MySQL, you might be confused by the temporary table concept in Oracle Database.

In Oracle Database, global temporary tables are permanent objects whose data are stored on disk and automatically deleted at the end of a session or transaction. In addition, global temporary tables are visible to all sessions currently connected to the database.

Oracle 18c introduced private temporary tables whose both table definition and data are temporary and are dropped at the end of a transaction or session. On top of that, Oracle stores private temporary tables in memory and each temporary table is only visible to the session which created it.

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Create private temporary table statement

To create a new private temporary table, you use the statement:

In this syntax:

First, specify the name of the temporary table, which follows the naming rule mentioned above.

Second, specify a list of columns with their definitions.

Third, use the clause to indicate whether the table is transaction-specific or session-specific:

  • The option creates a private temporary table that is transaction-specific. At the end of the transaction, Oracle drops both table definition and data.
  • The option creates a private temporary table that is session-specific. Oracle removes all data and drops the table at the end of the session.

By default, Oracle uses if you omit the option.

Is There A CREATE TABLE IF NOT EXISTS Command?

Some databases have CREATE TABLE IF NOT EXISTS, others don’t.

  • Oracle: No, but there is a workaround.
  • SQL Server: No, but there is a workaround.
  • MySQL: Yes, there is.
  • PostgreSQL: Yes, there is

As mentioned in this StackOverflow answer:

Oracle CREATE TABLE IF NOT EXISTS Equivalent

To check if a table exists before creating it, you’ll need to write a PL/SQL block. There are a few ways you can check:

  1. You can attempt to create a table, and catch the error that appears (ORA-00955: name is already in use by an existing object).
  2. You can query the USER_TABLES view to find a count where the table name matches, and check if the value is > 0.
  3. You can drop the table and then create it again.

I’ve written about finding a list of tables in Oracle here.

PostgreSQL CREATE TABLE IF NOT EXISTS

In PostgreSQL 9.1, this feature exists. You can simply add IF NOT EXISTS to the CREATE TABLE statement:

For earlier versions, one workaround is to use a function. But if you are creating a function to create a table if it doesn’t exist, perhaps there’s a better approach to your problem you can take.

Create a MySQL Table Using MySQLi and PDO

The CREATE TABLE statement is used to create a table in MySQL.

Notes on the table above:

The data type specifies what type of data the column can hold. For a complete reference of all the available data types, go to our Data Types reference.

After the data type, you can specify other optional attributes for each column:

  • NOT NULL — Each row must contain a value for that column, null values are not allowed
  • DEFAULT value — Set a default value that is added when no other value is passed
  • UNSIGNED — Used for number types, limits the stored data to positive numbers and zero
  • AUTO INCREMENT — MySQL automatically increases the value of the field by 1 each time a new record is added
  • PRIMARY KEY — Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT

Each table should have a primary key column (in this case: the «id» column). Its value must be unique for each record in the table.

The following examples shows how to create the table in PHP:

Create Primary Key — Using ALTER TABLE statement

You can create a primary key in Oracle with the ALTER TABLE statement.

Syntax


The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example

Let’s look at an example of how to create a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we’ve created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.

We could also create a primary key with more than one field as in the example below:

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

Look at the following two tables:

«Persons» table:

PersonID LastName FirstName Age
1 Hansen Ola 30
2 Svendson Tove 23
3 Pettersen Kari 20

«Orders» table:

OrderID OrderNumber PersonID
1 77895 3
2 44678 3
3 22456 2
4 24562 1

Notice that the «PersonID» column in the «Orders» table points to the «PersonID» column in the «Persons» table.

The «PersonID» column in the «Persons» table is the PRIMARY KEY in the «Persons» table.

The «PersonID» column in the «Orders» table is a FOREIGN KEY in the «Orders» table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

Using a CREATE TABLE statement

Syntax

The syntax for creating a foreign key using a CREATE TABLE statement is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

Example

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

In this example, we’ve created a primary key on the supplier table called supplier_pk. It consists of only one field — the supplier_id field. Then we’ve created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
);

Oracle CREATE TABLE Errors and Exceptions

These are some of the errors and exceptions that may appear when you’re creating a table. As a database developer, you’ll get these kinds of errors all the time!

Exception: ORA-00955: name is already used by an existing object

Reason: You’re attempting to create a table with a name that is already being used.

Resolution: Use a different name for your table, or drop the table with the existing name. Or, use a different schema for your table if that’s applicable.

Exception: ORA-02260: table can have only one primary key

Reason: You’re attempting to add a primary key to a table that already has one.

Resolution: Review your SQL CREATE TABLE statement and remove all but one PRIMARY KEY definition.

Exception: ORA-02267: column type incompatible with referenced column type

Reason: The column you’re referring to is not compatible with the column you’re defining it on. This could happen when defining a foreign key.

Resolution: Make sure the data types are the same for both columns. And, make sure you’re not referring to the same table for the foreign key (it should be a different table).

Exception: ORA-00904: : invalid identifier

Reason: There are many reasons for this error, but it’s usually a syntax error.

Resolution: Check that you have all of your commas and brackets in the right places in your statement.

Exception: ORA-00957: duplicate column name

Reason: You have two columns with the same name.

Resolution: Rename one of your columns to make sure it is not a duplicate. Also, check that your commas and brackets are all in the right places.

Oracle identity column examples

Let’s take some examples of using the Oracle identity columns.

A) example

The following statement creates a table named that consists of an identity column:

The following statement inserts a new row into the table:

Because we did not specify a value for the column, Oracle automatically generated a sequential value starting from 1.

The following statement attempts to insert a value into the identity column:

Oracle issued an error:

Because the id column was defined as , it could not accept any provided value.

B) example

Let’s change the column to :

The following statement inserts a new row into the table:

It worked as expected.

The following statement inserts a new row into the table with a provided value for the column:

In this example, Oracle used the provided value and inserted it to the table.

The following example attempts to insert a null value into the column:

Oracle issued an error:

C) example

First, change the column of the table to :


The following statement provides no value for the column, Oracle will automatically generate a value for insert:

The following statement inserts a NULL value into the id column because the id column has been defined as , Oracle generates a sequential value and uses it for insert:

D) option example

First, recreates the table whose the column is defined as identity column with the initial value starts from 100:

Second, insert a row into to the table:

Third, query data from the table:

As you can see, the initial value of the column is 100 as specified in identity clause.

E) option example

First, change the id column of the table that includes both and options.

Second, insert two rows into the table:

Third, query data from the table to verify the inserts:

As you can see, the first row has the id value 10. The second row has the id value 20. This is what we defined for the id column that should start with 10 and increase by 10 for the new row.

Introduction to Oracle foreign key constraint

A foreign key is all about the relationship. Let’s start with an example to clearly understand its concept.

Suppose, we have two tables and :

The table stores supplier groups e.g., one-time supplier, third-party supplier, and inter-co supplier. Each supplier group may have zero, one, or many suppliers.

The table stores the supplier information. Each supplier must belong to a supplier group.

The relationship between and table is one-to-many. In other words, one supplier group has many suppliers while each supplier must belong to a supplier group.

The in the table is used for establishing the relationship between rows in the and tables.

Before inserting a row into the table, you have to look up an existing in the table and use this value for insertion.

Assuming that the table contains the following data:

To insert a new third-party supplier, you have to use the 2 as follows:

It works perfectly fine. However, the following statement also works:

The table has no row with group id 4 but nothing prevents you from inserting it into the table, which is an issue.

For example, the following query fails to get all suppliers and their groups:

As you can see, the supplier is missing in the result set.

A solution to fix this problem is to use the Oracle foreign key constraint to enforce the relationship between rows from the and tables.

First, drop the table:

Second, recreate the  table with a foreign key constraint:

In this statement, the following clause was newly added:

This clause defines the column in the table as a foreign key that references to the column of the table.

This way, the constraint is enforced by Oracle. In other words, attempting to insert a row into the table that does not correspond to any row in the table will fail, as attempting to delete a row from the table where there exist dependent rows in the table.

The table is called a child table while the is referred to as a parent table. To extend the parent-child analogy, the primary key value is taken from the parent table () and is inserted into the child table () i.e., the child carries a as the parent’s DNA or genetic code.

By the way, the concept of referential integrity is all about maintaining and enforcing this parent-child relationship.

Oracle foreign key constraint in actions

The following statement works because the table has a row with 1:

However, the following statement will fail:

Because the has no row with id 4. The following is the error message:

Similarly, attempting to delete a row with 1 in the table will fail:

Oracle issued the following error message:

Because the table (child table) has a row that references to the row which is being deleted.

Using an ALTER TABLE statement

Syntax

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n);

Example

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id);

In this example, we’ve created a foreign key called fk_supplier that references the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field as in the example below:

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the «PersonID» column when the «Orders» table is created:

MySQL:

CREATE TABLE Orders (     OrderID int NOT NULL,     OrderNumber int NOT NULL,     PersonID int,     PRIMARY KEY (OrderID),     FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (     OrderID int NOT NULL PRIMARY KEY,     OrderNumber int NOT NULL,     PersonID int FOREIGN KEY REFERENCES Persons(PersonID) );

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:


CREATE TABLE Orders (     OrderID int NOT NULL,     OrderNumber int NOT NULL,     PersonID int,     PRIMARY KEY (OrderID),     CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)     REFERENCES Persons(PersonID) );

Practice Exercise #3:

Based on the departments table below, create an Oracle table called employees that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create a foreign key on the employees table that references the departments table based on the department_id field.

CREATE TABLE departments
( department_id number(10) NOT NULL,
  department_name varchar2(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #3:

The Oracle CREATE TABLE statement for the employees table is:

CREATE TABLE employees
( employee_number number(10) NOT NULL,
  employee_name varchar2(50) NOT NULL,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

CREATE TABLE AS SELECT Without Copying Data

You can also use the CREATE TABLE AS SELECT to copy a table’s structure without any of the data.

It’s easier than trying to generate a create table script from an existing table.

To do this, you just adjust your WHERE clause in the SELECT query to ensure there are no rows returned.

How do you guarantee this?

By adding something that is clearly evaluated to FALSE.

A common example is WHERE 1=0. Or WHERE 1=2. Really, anything involving two numbers that are not equal will work.

So, the SELECT statement will return the column names and data types, but no data. When the table is created, it will have no data in it.

The syntax for this would be:

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the «ID» column when the «Persons» table is created:

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (     ID int NOT NULL UNIQUE,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Age int );

MySQL:

CREATE TABLE Persons (     ID int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Age int,     UNIQUE (ID) );

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (     ID int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Age int,     CONSTRAINT UC_Person UNIQUE (ID,LastName) );

CREATE TABLE Examples

Alright, now it’s time to look at some examples of creating a table.

These CREATE TABLE examples cover all of the topics I’ve mentioned in this article. It usually helps to see examples with data and real names, rather than syntax.

Each of the examples demonstrates the SQL using syntax for Oracle, SQL Server, MySQL, and PostgreSQL.

Example 1 – Basic Table

This example uses a CREATE TABLE statement that creates a simple table with a couple of columns.

Database SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 2 – Large Table

This CREATE TABLE example includes many different columns and data types.

Database SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 3 – Large Table with NOT NULL and DEFAULT

This CREATE TABLE example is similar to Example 2 but includes several NOT NULL constraints and some default values.

Database SQL
Oracle
SQL Server
MySQL
PostgreSQL

Note: Prior to MySQL v8.0.13, using a function or expression (such as CURRENT_DATE) as a column’s default value was not supported. From the MySQL documentation:

As of 8.0.13 this now works. You can set CURRENT_DATE as the default for a date column.

Example 4 – Inline Primary Key

This example of the CREATE TABLE statement uses a primary key that is defined inline.

The syntax is the same for all databases, only the data types of the columns are different.

Database SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 5 – Inline Primary Key and Foreign Key

This CREATE TABLE example uses an inline primary key and inline foreign key.

The syntax is the same for all databases, only the data types of the columns are different.

Database SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 6 – Out of Line Primary Key and Foreign Key

This Oracle CREATE TABLE example declares a primary key and foreign key out of line (at the end of the column declarations).

The syntax is the same for all databases, only the data types of the columns are different.

Database SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 7 – More Constraints

This example uses CREATE TABLE to declare a table with a primary key, foreign key, unique constraint, and check constraint.

Database SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 8 – Create Table as Select with All Columns

This example uses the Create Table as Select to create a table from another table, using all columns.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

Example 9 – Create Table as Select with Some Columns

This example uses the Create Table as Select to create a table from another table, using only some of the columns.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

Example 10 – Create Table as Select with No Data

This example uses the Create Table as Select to create a table from another table, but no data is added to the new table.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

Create Primary Key — Using CREATE TABLE statement

You can create a primary key in Oracle with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

Example

Let’s look at an example of how to create a primary key using the CREATE TABLE statement in Oracle:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we’ve created a primary key on the supplier table called supplier_pk. It consists of only one field — the supplier_id field.

We could also create a primary key with more than one field as in the example below:


С этим читают