Difference between revisions of "Table Creation and Management"

From rbachwiki
Jump to navigation Jump to search
 
(29 intermediate revisions by the same user not shown)
Line 1: Line 1:
;Identify the table name and structure
;Identify the table name and structure
: Commands used to create or modify database tables are called '''data definition language(DDL'''
: Commands used to create or modify database tables are called '''data definition language(DDL''' commands
;Create a new table with the CREATE TABLE command
: A '''Database Object''' is a defined self contained structure also called database tables
;Use a subquery to create a new table
{| class="wikitable"
;Add a column to an existing table
!Creating Tables
;Modify the definition of a column in an existing table
!Description
;Delete a column from an existing table
|-
;Mark a column as unused and then delete it later
| CREATE TABLE
;Rename a table
| Creates a new table in the database. The user names the columns and identifies the type of data to be stored. To view a table, use the SQL*PLUS
;Truncate a table
command DESCRIBE.
;Drop a table
|-
!colspan="2"|Modifying Tables
|-
|ALTER TABLE . . . ADD
|Adds a column to a table.
|-
|ALTER TABLE . . . MODIFY
|Changes a column size, datatype, or default value.
|-
|ALTER TABLE . . . DROP COLUMN
|Deletes one column from a table
|-
|ALTER TABLE . . . SET UNUSED or SET UNUSED COLUMN
|Marks a column for deletion at a latertime.
|-
|}
 
= Modifying Existing Tables =
==Drop (Delete a Column) ==
ALTER TABLE tablename
DROP COLUMN columnname;
 
== Adding a column to an existing Table ==
'''This adds ext to the end of the publisher table '''
ALTER TABLE publisher
ADD (ext NUMBER(4));
 
== MODIFY COMMAND Change a column size, datatype or default value ==
* Changing Column Size (increase or decrease)
*Changing the datatype (such as varchar2 to CHAR)
*Changing or adding the default value of a column (such as DEFAULT SYSDATE)
 
ALTER TABLE books
MODIFY (title VARCHAR2(10));
 
ALTER TABLE publisher
MODIFY (rating DEFAULT 'N');
 
== Adding Primary Key Constraints ==
'''This makes customer# the primary key  the constraint name is used so you can easily '''
'''identify an error if you try to enter a duplicate customer'''
'''If you don’t assign constraint names, the error message displays the'''
'''system-generated constraint name, which isn’t as helpful'''
ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);
 
== DROP PRIMARY KEY ==
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
 
== Foreign Key Constraint ==
'''This command instructs Oracle 11g to add a FOREIGN KEY constraint on the'''
'''Customer# column of the ORDERS table. The name chosen for the constraint is'''
'''orders_customer#_fk. This constraint makes sure an entry for the Customer# column of'''
'''the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS'''
'''table. When the command executes, a message indicates the table was altered successfully'''
ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)
== Unique Constraints ==
ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);
== Check Constraint ==
ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);
 
== Not Null Constraint ==
ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);
=Creating NEW Tables =
CREATE TABLE a_animals(
animal_id NUMBER(6),
  name VARCHAR2(25),
license_tag_number NUMBER(10),
admit_date DATE CONSTRAINT ail_admit_date_nn NOT NULL,
adoption_id NUMBER(5),
vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,
  CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),
  CONSTRAINT ail_license_pk UNIQUE(license_tag_number)
);
== Default Values ==
CREATE TABLE acctmgr(
id CHAR(4),
am_date DATE DEFAULT SYSDATE,
am_count NUMBER(7,2) DEFAULT 0,
);
 
== Creating a table based on another table (subquery) ==
CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);
 
== ADDING COMMENTS TO COLUMNS ==
''' PROBLEMS IS THE TABLE.DESCRIPTION IS THE COLUMN '''
COMMENT ON COLUMN problems.description IS 'Short description of the problem';
 
[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]

Latest revision as of 20:09, 12 November 2017

Identify the table name and structure
Commands used to create or modify database tables are called data definition language(DDL commands
A Database Object is a defined self contained structure also called database tables
Creating Tables Description
CREATE TABLE Creates a new table in the database. The user names the columns and identifies the type of data to be stored. To view a table, use the SQL*PLUS

command DESCRIBE.

Modifying Tables
ALTER TABLE . . . ADD Adds a column to a table.
ALTER TABLE . . . MODIFY Changes a column size, datatype, or default value.
ALTER TABLE . . . DROP COLUMN Deletes one column from a table
ALTER TABLE . . . SET UNUSED or SET UNUSED COLUMN Marks a column for deletion at a latertime.

Modifying Existing Tables

Drop (Delete a Column)

ALTER TABLE tablename
DROP COLUMN columnname;

Adding a column to an existing Table

This adds ext to the end of the publisher table

ALTER TABLE publisher
ADD (ext NUMBER(4));

MODIFY COMMAND Change a column size, datatype or default value

  • Changing Column Size (increase or decrease)
  • Changing the datatype (such as varchar2 to CHAR)
  • Changing or adding the default value of a column (such as DEFAULT SYSDATE)
ALTER TABLE books
MODIFY (title VARCHAR2(10));
ALTER TABLE publisher
MODIFY (rating DEFAULT 'N');

Adding Primary Key Constraints

This makes customer# the primary key  the constraint name is used so you can easily 
identify an error if you try to enter a duplicate customer
If you don’t assign constraint names, the error message displays the
system-generated constraint name, which isn’t as helpful
ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);

DROP PRIMARY KEY

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Foreign Key Constraint

This command instructs Oracle 11g to add a FOREIGN KEY constraint on the Customer# column of the ORDERS table. The name chosen for the constraint is orders_customer#_fk. This constraint makes sure an entry for the Customer# column of the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS table. When the command executes, a message indicates the table was altered successfully

ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)

Unique Constraints

ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);

Check Constraint

ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);

Not Null Constraint

ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);

Creating NEW Tables

CREATE TABLE a_animals(
animal_id NUMBER(6),
 name VARCHAR2(25),
license_tag_number NUMBER(10),
admit_date DATE CONSTRAINT ail_admit_date_nn NOT NULL,
adoption_id NUMBER(5),
vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL,
  CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id),
  CONSTRAINT ail_license_pk UNIQUE(license_tag_number)
);

Default Values

CREATE TABLE acctmgr(
id CHAR(4),
am_date DATE DEFAULT SYSDATE,
am_count NUMBER(7,2) DEFAULT 0,

);

Creating a table based on another table (subquery)

CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);

ADDING COMMENTS TO COLUMNS

PROBLEMS IS THE TABLE.DESCRIPTION IS THE COLUMN

COMMENT ON COLUMN problems.description IS 'Short description of the problem';

Back To Top- Home - Category