ORACLE CREATE TABLE

 

Oracle CREATE TABLE

Presentation to Oracle CREATE TABLE statement 

To create a table in Oracle Database, you use the CREATE TABLE statement. The following illustrates the basic syntax of the CREATE TABLE statement:

 

CREATE  TABLE  table-name ( 

        column_1 data_type column_constraint, column_1 data_type column_constraint, 

        column_2 data_type column_constraint, 

        ... 

        table_constraint 

    ); 

In this syntax: 

Promote first, specify the name of the table and the name of the diagram to which the new table belongs in the create TABLE statement. 

Second, list all the columns on the table in brackets. In case a table has several columns, you must separate them standard from the commas (.). A column definition includes the column name followed by child type of data, standard example, NUMBER, VARCHAR2, and a column constraint such as NOT NULL, primary key, check. 

Third, add table constraints, if any, example standard, primary key, foreign key, check. 

Note that you must have the CREATE TABLE system privilege to create a new table in your schema and create a TABLE system privilege to create a new table in another user's schema. In addition to this, the owner of the new table must have the quantity for the table space that contains the new table or the privilege of the TABLESPACE UNLIMITED system. 

Example of Oracle CREATE TABLE statement 

The following example shows remark creating a new table called people in the ot composition: 

CREATE TABLE person ( 

PERSON_ID NUMBER generated BY DEFAULT IN THAT IDENTITY, 

first_name VARCHAR2(50) NOT NULL, 

last_name VARCHAR2(50) NOT NULL, 

PRIMARY CLE (person_id) 

); 

In this example, the people's table has three columns: person_id, first_name and last_name. 

The person_id is the identity column that identifies the unique lines in the table. The type of data in the person_id column is NUMBER. The statement generated BY DEFAULT as Oracle IDENTITY instructs to generate a whole new one for the column each time a new line is inserted into the table. 

The first_name column has the type of  VARCHAR2 data with a maximum length of 50. This means that you cannot insert a first name longer than 50 in the first_name column. In addition, the NOT NULL column constraint prevents the first_name column from having NULL values. 

The last_name column has the same characteristics as the first_name column. 

The PRIMARY CLE statement specifies the person_id column as the primary key column used to identify the remarkable line in the people table. 

In this tutorial, you learned how to use Oracle CREATE TABLE to create a new table.

Example : 

create table admin(

id varchar2(20),

username varchar2(20),

password varchar2(20),

constraint pk_admin primary key (id)

); 

What is an essential Key or  Primary key in Oracle? 

In Oracle, an essential key is a solitary field or a mix of fields that just characterizes a record. None of the fields that literary style some portion of the essential key can contain an invalid worth. A table can just have one essential key. 

Note 

In Oracle, an essential key can't contain notwithstanding 32 sections. 

An essential key can be set in a CREATE TABLE direction or in an ALTER TABLE direction. 

Make an essential key - utilizing the CREATE TABLE assertion 

You can make an essential key in Oracle with the CREATE TABLE assertion. 

Language structure 

The language structure to make an essential key utilizing the CREATE TABLE assertion in Oracle/PLSQL is as per the following: 

Syntax :

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

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

Model 

We should take a gander at a case of making an essential key utilizing the CREATE TABLE assertion in Oracle: 

Example :

create table Langue(
id number,
langue varchar2(20),
niveau varchar2(20),
price number, 
constraint pk_langue primary key (id));

What is a foreign key in Oracle? 

A foreign key is a way to apply referential integrity in your Oracle database. A foreign key means that the values of one scene must also appear in another scene. 

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table usually refers to a primary key in the parent table. 

A foreign key can be set in a CREATE TABLE guidance or in an ALTER TABLE guidance. 

Use of CREATE TABLE guidance 

Syntax :

The syntax to create a foreign key using a CREATE TABLE guidance is as follows: 

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 blog
( id number not null,
  name varchar2(50) not null,
  date Date,
  CONSTRAINT supplier_pk PRIMARY KEY (id)
);

CREATE TABLE article
( Article_id number not null,
  Title_id numeric(10) not null, id number not null,
    CONSTRAINT PK_article PRIMARY KEY(Article_id),

    CONSTRAINT fk_article FOREIGN KEY (id)
    REFERENCES supplier(id)
);