XV, Oracle constraints

I. Maintaining the integrity of the data
Data integrity is used to ensure that database data complies with certain commercial and logical rules, and in Oracle, data integrity can be implemented using three methods of constraints, triggers, applications (procedures, functions), in three ways, because constraints are easy to maintain and have the best performance. So as the first choice to maintain data integrity.

Ii. constraints
Constraints are used to ensure that database data meets specific business rules. In Oracle, constraints include: NOT NULL, unique, primary key, foreign key, and check five.
1), not NULL (non-NULL)
If not NULL is defined on a column, data must be provided for the column when inserting data.
2), unique (unique)
When a unique constraint is defined, the column value cannot be duplicated, but can be null.
3), primary key (primary key)
Used to uniquely indicate the data of a table row, when a primary key constraint is defined, the column cannot be duplicated and cannot be null.
It is necessary to note that a table can have a maximum of one primary key, but there may be multiple unqiue constraints.
4), foreign key (foreign key)
Used to define the relationship between the primary table and the table. FOREIGN KEY constraints are defined on the table, the primary table must have a PRIMARY KEY constraint or a unique constraint, and when the foreign key constraint is defined, the foreign key column data must exist or be NULL in the primary key column of the main table.
5), check
A condition that forces the row data to be met, assuming that a check constraint is defined on the SAL column and that the SAL column value is not between 1000-2000 and an error is indicated between 1000-2000.

Third, the shop Sales system table design case One
A database of existing stores, recording customers and their purchases, consists of the following three tables:
Commodity Goods (product No. goodsid, commodity name Goodsname, Unit price UnitPrice, category of goods, supplier provider);
Customer (Customer number CustomerID, name name, address, email, gender sex, ID card cardid);
Purchase Purchase (Customer number CustomerID, product number GOODSID, purchase quantity nums);
Please complete the following functions in SQL language:
1. Create a table and declare it in the definition:
(1). The primary foreign key for each table;
(2). The customer's name can not be null value;
(3). Unit price must be greater than 0, purchase quantity must be between 1 to 30;
(4). e-mails cannot be duplicated;
(5). The gender of the client must be male or female, the default is male;

Sql> CREATE TABLE Goods (
   Goodsid Char (8) Primary key,--primary key
   Goodsname VARCHAR2 (30),
   UnitPrice Number (10,2) check (unitprice>0),
   Category VARCHAR2 (8),
   Provider Varchar2 (30)
);

   CustomerId Char (8) Primary key,--primary key
   Name VARCHAR2 () not NULL,--no null
   Address VARCHAR2 (50),
   Email varchar2 (unique)--the only
   Sex char (2) Default ' male ' Check (sex in (' Male ', ' female ')),--one char can save half of Chinese characters, two char can save a Chinese character
   CardId Char (18)
);

   CustomerId Char (8) References customer (CUSTOMERID),
   Goodsid Char (8) References goods (GOODSID),
   Nums number Check (Nums between 1 and 30)
);
Tables are built by default in the system table space

Four, shop Sales system table design case Two
if you forget to establish the necessary constraints while building a table, you can use the ALTER TABLE command to add constraints to the table after you build the table. Note, however, that when you add a NOT NULL constraint, you need to use the Modify option, and add the additional four constraints using the Add option.
1), add product name also cannot be empty
sql> ALTER TABLE goods modify goodsname not null;
2), add ID card can not repeat
sql> ALTER TABLE customer add constraint xxxxxx unique (cardId);
3), increase the customer's address can only be ' Haidian ', ' Chaoyang ', ' Dongcheng ', ' Xicheng ', ' Tongzhou ', ' Chongwen ', ' changping ';
sql> ALTER TABLE customer add constraint yyyyyy check (address In (' Haidian ', ' Chaoyang ', ' Dongcheng ', ' Xicheng ', ' Tongzhou ', ' Chongwen ', ' changping '));

Delete Constraint
When you no longer need a constraint, you can delete it.
ALTER TABLE name DROP CONSTRAINT constraint name;
In particular, there may be errors when deleting a primary key constraint, such as the ALTER TABLE name drop PRIMARY key, because if there is a master-slave relationship between the two tables, you must take the CASCADE option when deleting the primary KEY constraint for the primary table, such as: Alter Table Name drop PRIMARY key cascade;

Display constraint information
1), display constraint information
By querying the data dictionary view user_constraints, you can display information about all constraints for the current user.
Select Constraint_name, Constraint_type, status, validated from user_constraints where table_name = ' table name ';
2), display constraint columns
By querying the data dictionary view user_cons_columns, you can display the table column information for the constraint.
Select column_name, Position from user_cons_columns where constraint_name = ' constraint name ';
3), of course, there are easier methods, directly with PL/SQL developer view can be. A brief demonstration of the next ...

V. Table-level definition, column-level definition
1), column-level definition
A column-level definition defines a constraint while defining a column.
If you define a PRIMARY KEY constraint in the Department table

CREATE TABLE Department4 (
   dept_id number (constraint) Pk_department primary key,
   
   Loc VARCHAR2 (12)
);

2), table-level definition
A table-level definition defines a constraint after all columns have been defined. Here's what to note:
A NOT NULL constraint can only be defined at the column level.
To define a PRIMARY key constraint and a FOREIGN KEY constraint when establishing a employee2 table, for example:

CREATE TABLE Employee2 (
   
   Name VARCHAR2 (15),
   
   Constraint Pk_employee primary KEY (emp_id),
   Constraint fk_department foreign KEY (dept_id) references Department4 (dept_id)
);

XV, Oracle constraints