Creating TablesBefore tables can be used, they have to be created and this is generally the job of the DBA. Creating tables is done with the create table command. The create table command does the following:
* Defines the table name
* Defines the columns in the table and the datatypes of those columns
* Defines what tablespace the table resides in (optional)
* Defines other characteristics of the table (optional)
Let’s look at the create table command in action:
Connect scott/tiger CREATE TABLE books ( book_id NUMBER PRIMARY KEY, book_name VARCHAR2(30), author_name VARCHAR2(40), book_isbn VARCHAR2(20) )TABLESPACE users;In this example, we create a table called BOOKS which has 4 columns. The first column is BOOK_ID which is a NUMBER datatype. This means we will be storing numbers in this column. Did you notice on the line where we define the column BOOK_ID, that we included the word primary key?
This is known as an in-line constraint because we are defining the constraint on the same line as the column associated with the constraint. In this case the keyword primary key means we are defining a primary key constraint on the BOOKS table called PK_BOOKS.
What is a primary key constraint? A constraint is a rule that is applied to the table. In this case, the primary key constraint is a rule is says can’t have a duplicate entry in the BOOK_ID column, and the BOOK_ID column can never be empty or “null.” In this example, each book has a unique BOOK_ID assigned. In other words, War and Peace might have a BOOK_ID of 12345, and no other book will ever have the same BOOK_ID column value.
You might say, why not just make the title of the book the primary key. The answer is that you might have a number of different books called War and Peace. One might be paperback and one might be hardcover. One might be out of print, one might be an easy to read child’s version. Hence, the title of the book isn’t a good candidate to uniquely identify the book. In this case then, we have a column called BOOK_ID that will be unique for each book. It then becomes the primary key. Since this isn’t a book about designing databases, that’s enough on primary keys for now.
You should know that in Oracle each primary key column must be unique. Thus, we can’t have two books with a BOOK_ID of 12345, Oracle would reject the second attempt to use that BOOK_ID and return an error to the user.
You can also define a combination of columns to be the primary key. This is known as a concatenated primary key. These kinds of constraints are defined as out-of-line constraints because they are defined on their own line in the create table statement.
Here is an example of the creation of an out-of-line primary key constraint. In this case our books table might well have multiple BOOK_ID’s. Hence, we will add a sequence number column (BOOK_ID_SEQ) to act as a “tie breaker”. Both columns in this example
CREATE TABLE books ( book_id NUMBER, book_id_seq NUMBER, book_name VARCHAR2(30), author_name VARCHAR2(40), book_isbn VARCHAR2(20), CONSTRAINT pk_books PRIMARY KEY (book_id, book_id_seq) )TABLESPACE users;Once you create a table, you can use the SQL*Plus desc command to see its structure as seen in this example:
SQL>desc books Name Null? Type ----------------------------------------- -------- ------------ BOOK_ID NOT NULL NUMBER BOOK_ID_SEQ NOT NULL NUMBER BOOK_NAME VARCHAR2(30) AUTHOR_NAME VARCHAR2(40) BOOK_ISBN VARCHAR2(20)
* Defines the table name
* Defines the columns in the table and the datatypes of those columns
* Defines what tablespace the table resides in (optional)
* Defines other characteristics of the table (optional)
Let’s look at the create table command in action:
Connect scott/tiger CREATE TABLE books ( book_id NUMBER PRIMARY KEY, book_name VARCHAR2(30), author_name VARCHAR2(40), book_isbn VARCHAR2(20) )TABLESPACE users;In this example, we create a table called BOOKS which has 4 columns. The first column is BOOK_ID which is a NUMBER datatype. This means we will be storing numbers in this column. Did you notice on the line where we define the column BOOK_ID, that we included the word primary key?
This is known as an in-line constraint because we are defining the constraint on the same line as the column associated with the constraint. In this case the keyword primary key means we are defining a primary key constraint on the BOOKS table called PK_BOOKS.
What is a primary key constraint? A constraint is a rule that is applied to the table. In this case, the primary key constraint is a rule is says can’t have a duplicate entry in the BOOK_ID column, and the BOOK_ID column can never be empty or “null.” In this example, each book has a unique BOOK_ID assigned. In other words, War and Peace might have a BOOK_ID of 12345, and no other book will ever have the same BOOK_ID column value.
You might say, why not just make the title of the book the primary key. The answer is that you might have a number of different books called War and Peace. One might be paperback and one might be hardcover. One might be out of print, one might be an easy to read child’s version. Hence, the title of the book isn’t a good candidate to uniquely identify the book. In this case then, we have a column called BOOK_ID that will be unique for each book. It then becomes the primary key. Since this isn’t a book about designing databases, that’s enough on primary keys for now.
You should know that in Oracle each primary key column must be unique. Thus, we can’t have two books with a BOOK_ID of 12345, Oracle would reject the second attempt to use that BOOK_ID and return an error to the user.
You can also define a combination of columns to be the primary key. This is known as a concatenated primary key. These kinds of constraints are defined as out-of-line constraints because they are defined on their own line in the create table statement.
Here is an example of the creation of an out-of-line primary key constraint. In this case our books table might well have multiple BOOK_ID’s. Hence, we will add a sequence number column (BOOK_ID_SEQ) to act as a “tie breaker”. Both columns in this example
CREATE TABLE books ( book_id NUMBER, book_id_seq NUMBER, book_name VARCHAR2(30), author_name VARCHAR2(40), book_isbn VARCHAR2(20), CONSTRAINT pk_books PRIMARY KEY (book_id, book_id_seq) )TABLESPACE users;Once you create a table, you can use the SQL*Plus desc command to see its structure as seen in this example:
SQL>desc books Name Null? Type ----------------------------------------- -------- ------------ BOOK_ID NOT NULL NUMBER BOOK_ID_SEQ NOT NULL NUMBER BOOK_NAME VARCHAR2(30) AUTHOR_NAME VARCHAR2(40) BOOK_ISBN VARCHAR2(20)
No comments:
Post a Comment