Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 7: Constraints
Similarly in this case too Oracle Instance will make sure before
inserting or updating any information into this “suppliers” table that
the supplier_id lies between 100 and 9999. If it doesn’t then the user
will get a “constraint violation” error and the request will not get
processed.
Lets now talk about Primary Key constraint. Once you implement this
constraint on any column, you would not be able to insert duplicate and
NULL value i.e. each value will be a unique identifier for that whole
record/row. Moreover Oracle creates a implicit index on that column.
Index created by Oracle for any column of a table is having the same
analogy of index we are having at the end of each book. Indexes created
by Oracle serve the same purpose as the indexes at the end of books i.e.
helps in speeding up the search process.
Example:
CREATE TABLE dept_test
(deptno NUMBER(3) CONSTRAINT pk_dept_test PRIMARY KEY,
dname VARCHAR2(15),
loc VARCHAR2(10)
);
Once you have created this table “dept_test” start inserting values in
it. Remember since you have created a PRIMARY KEY constraint on deptno
column you would not be able to insert NULL value or any thing that was
already there in deptno but can insert NULL or duplicate values in rest
of the columns. Also Oracle will create a implicit index on this primary
key column because Oracle presumes that you would be using this column a
lot in your search criteria (WHERE clause) and there are bright chances
that you may link this table with some other table using FOREIGN KEY
constraint.
Chapter 7: Constraints
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: