Table: Tables are basic units of data storage. A table is defined as
intersection of rows and columns. Data is stored in rows and columns.
CREATING TABLES: A table name can not exceed 30 characters and
necessarily should start with a character. Blank spaces and other special
characters except an “under score” ( _ ) are not allowed in table names.
Each column in the table will be given a unique name. Every
column is assigned a data type corresponding to the data to be entered into the
column. Column names are also restricted to 30 characters and follow the same
rules as table names.
Syntax
for creating a Table:CREATE TABLE <Table Name>
(<Column Name1> <Data Type>,<Column Name2> <Data Type>, <Column Name1> <Data Type>);
Example:
DROPPING A TABLE: To drop a table it must already exist in the
database. The syntax for DROP TABLE statement is DROP TABLE <Table Name>;
Constraints: An integrity constraint defines a business rule
for a table column. When enabled, the rule will be enforced by oracle. The two
basic
Integrity Constraints Supported by Oracle
1) NOT NULL: A column with this constraint will not allow NULL values.
2) PRIMARY KEY: There can be only one primary key column in a table. This will only UNIQUE values. Does not allow NULL values.
3) UNIQUE KEY: We can have any number of primary keys in a table. UNIQUE Constraint also would accept only UNIQUE values.
4) CHECK: This constraint defines a condition which need to be satisfied by the value entering into the table.
5) FOREIGN KEY: A foreign key is a combination of columns with values based primary. It is also known as referential integrity constraint. Values that a foreign key can take are the values that are present in primary key.
2) PRIMARY KEY: There can be only one primary key column in a table. This will only UNIQUE values. Does not allow NULL values.
3) UNIQUE KEY: We can have any number of primary keys in a table. UNIQUE Constraint also would accept only UNIQUE values.
4) CHECK: This constraint defines a condition which need to be satisfied by the value entering into the table.
5) FOREIGN KEY: A foreign key is a combination of columns with values based primary. It is also known as referential integrity constraint. Values that a foreign key can take are the values that are present in primary key.
Creating Table Using Constraints:
Note:
In the above screen shot lines after “----“ are comments. Different ways of
defining constraints are given above.
ALTER TABLE command is used for modifying structure of the table.
1) 1) Adding and deleting or renaming columns.
2) 2) Increasing and decreasing column data size.
3) 3) Changing column data type.
4) 4) Enable or Disable constraints.
Syntax:
ALTER TABLE
ADD | MODIFY | DROP | ENABLE | DISABLE |
The following screen shots illustrate how to add a column and how to remove a column.
ALTER TABLE command is used for modifying structure of the table.
1) 1) Adding and deleting or renaming columns.
2) 2) Increasing and decreasing column data size.
3) 3) Changing column data type.
4) 4) Enable or Disable constraints.
Syntax:
ALTER TABLE
ADD | MODIFY | DROP | ENABLE | DISABLE |
The following screen shots illustrate how to add a column and how to remove a column.
ALTER TABLE can be used to add and remove constraints as shown below
No comments:
Post a Comment