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 TableGoods (GoodsidChar(8)Primary Key,--PRIMARY KeyGoodsnamevarchar2( -), UnitPrice Number(Ten,2)Check(UnitPrice>0), categoryvarchar2(8), providervarchar2( -)); SQL> Create TableCustomer (CustomerIdChar(8)Primary Key,--PRIMARY KeyNamevarchar2( -) not NULL,--is not emptyAddressvarchar2( -), emailvarchar2( -)Unique,--onlySexChar(2)default 'male' Check(Sexinch('male','female')),--a char can save half a character, two char can save a Chinese characterCardIdChar( -)); SQL> Create TablePurchase (CustomerIdChar(8)ReferencesCustomer (customerId), GoodsidChar(8)ReferencesGoods (GOODSID), Nums Number(Ten)Check(Numsbetween 1  and  -) ; The table is 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 a constraint is no longer needed, you can delete it.
ALTER TABLE name DROP constraint constraint name;
specifically: When deleting a primary KEY constraint, there may be errors, such as the ALTER TABLE name drop PRIMARY key, because if there is a master-slave relationship between the two tables, Then, when you delete the primary KEY constraint for the primary table, you must bring the CASCADE option like this: 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 following ...

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 (   numberconstraintprimarykey,    varchar2 (),     varchar2 ());

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 TableEmployee2 (emp_id Number(4), namevarchar2( the), dept_id Number(2),    constraintPk_employeePrimary Key(emp_id),constraintFk_departmentForeign Key(dept_id)Referencesdepartment4 (dept_id));