Five types of constraints in the oracle--database

Five types of constraints in the database

Five kinds of constraints in   database and their adding methods five constraints 1.--PRIMARY KEY constraint (Primay key coustraint) uniqueness, non-nullability  2.--unique constraint (unique counstraint) uniqueness, can be empty, but only one The  3.--check constraint (check counstraint) limits the range, format of the column data (for example, age, gender, and so on)  4.--default constraint (default counstraint) that data defaults  5.-- FOREIGN KEY constraints (Foreign key Counstraint) need to establish a relationship between two tables and reference the syntax example of the main table's Column five constraints  1.--add a PRIMARY KEY constraint (Stuno as the primary key)  alter table stuinfo  Add constraint Pk_stuno primary key (Stuno)  2.--adding a unique constraint (the ID number is unique because everyone is different)  alter table Stuinfo add Constraint Uq_stuid Unique (stuid)  3.--add Default constraint (if address is not filled by default to "address Unknown")  alter table Stuinfo add constraint df_ Stuaddress default (' Address Unknown ') for stuaddress 4.--add Check constraint (age limit between 15-40 years)  alter table Stuinfo add Constraint ck_stuage Check (stuage between and +) ALTER TABLE Stuinfo add constraint ck_stusex check (stusex= ' man ' O R stusex= ' 5.--add foreign KEY constraint (primary table stuinfo and relationship from table stumarks, associated field Stuno) ALTER TABLE STUINFO ADD constraint Fk_stuno Foreign Key (Stuno) references Stuinfo (Stuno)   constraints (Constraint) are Microsoft SQL ServerA method for automatically maintaining database integrity, defining constraints that can be used to enter data in a single column of a table or table (see Chapter 9th for an Introduction to data integrity). There are 5 constraints in SQL Server: PRIMARY KEY constraint (Primary key Constraint), foreign keyword constraint (Foreign key Constraint), uniqueness constraint (unique Constraint), check constraint CONSTRAINT) and DEFAULT constraints (default Constraint). 1 PRIMARY KEY constraint   PRIMARY KEY constraint the value of the combination of one or more columns of the specified table is unique in the table, that is, it is possible to specify a single row of records. Only one column per table is designated as the primary keyword, and columns of the image and text types cannot be specified as the primary keyword, nor does it allow the specified primary key column to have a null attribute.   The syntax for defining a PRIMARY keyword constraint is as follows:  constraint constraint_name primary key [CLUSTERED | nonclustered]  (column_name1[, column_name2,..., Column_name16])   The parameters are described below: constraint_name  Specifies the name of the constraint's name constraint. Should be unique in the database. If not specified, the system automatically generates a constraint name.  clustered | nonclustered  Specifies the index category, CLUSTERED is the default value. For specific information, see the next chapter.  column_name  Specifies the name of the column that makes up the primary key. The primary key consists of up to 16 columns. Example 7-3: Create a Product information table with the product number and name as the primary keyword  create table products ( p_id char (8) not null, p_name char (TEN) not NULL, &NB Sp;price Money default 0.01,  quantity smallint null,  constraint pk_p_id primary KEY (p_id, P_name)  ) on [ PRIMARY]2 outside keyword constraints   foreign keyword constraints define the relationships between tables. When the combination of one or more columns in a table is the same as the primary key definition in other tables, you can define the combination of these columns or columns as a foreign keyword, and set which columns in which table it is appropriate to associate. In this way, when the column values are updated in the table that defines the primary KEY constraint, the outer key column in the table with the OUTER keyword constraint associated with it in the other table is also updated accordingly. The effect of the outer keyword constraint is also that when inserting data into a table that contains an outer key, the system rejects inserting the data if the table associated with it does not have the same value as the inserted outer key column value. As with the primary key, you cannot create a foreign keyword with a column that is defined as a TEXT or image data type. The outer keyword consists of up to 16 columns.   The syntax for defining the OUTER keyword constraint is as follows:  constraint constraint_name foreign key (column_name1[, column_name2,..., column_name16 ])  references ref_table [(Ref_column1[,ref_column2,..., Ref_column16])] [on DELETE {CASCADE | NO ACTION}] [on UPDATE {CASCADE | NO ACTION}]] [not for REPLICATION]  each parameter description is as follows:references  specifies the information to establish the associated table.  ref_table  Specifies the name of the table to which the association is to be established.  ref_column  Specifies the name of the related column in the table to which the association is to be established.  on DELETE {CASCADE | NO action}  Specifies the related actions that are made to the associated table when data is deleted from the table. In cases where a data row in a child table is associated with a corresponding data row in the parent table, if a value of cascade is specified, the corresponding data row in the child table is deleted when the parent table data row is deleted, and if no action is specified, SQL Server generates an error and rolls back the delete operation in the parent table. The NO ACTION is the default value.  on UPDATE {CASCADE | NO action}  Specifies the related actions that are made to the associated table when the data in the table is updated. In cases where a data row in a child table is associated with a corresponding data row in the parent table, if a value of cascade is specified, the corresponding data row in the child table is updated when the parent table data row is updated; If no ACTION is specified, SQL ServER generates an error and rolls back the update operation in the parent table. The NO ACTION is the default value.  not for replication  the outer keyword constraint of the specified column does not occur when data copied from other tables is inserted into the table.   Example 7-4: Create an order form associated with the previously created Product table  create table   orders ( order_id char (8),  p_id char (8),  p_ Name Char (ten),  constraint pk_order_id primary KEY (order_id),  foreign key (p_id, p_name) references products (p _id, P_name)  ) on [primary]  Note: temporary tables cannot specify an outer keyword constraint. Uniqueness constraint   Uniqueness constraint specifies that the value of a combination of one or more columns is unique to prevent duplicate values from being entered in the column. A Uniqueness constraint specifies that the column can have a null property. Because the primary key value is unique, the primary key column no longer sets a uniqueness constraint. A uniqueness constraint consists of up to 16 columns.   The syntax for defining uniqueness constraints is as follows:  constraint Constraint_name unique [CLUSTERED | nonclustered]  (column_name1[, column_name2,..., Column_name16]) Example 7-5: Define an Employee information table where the employee's identity card number is unique. CREATE TABLE Employees ( emp_id char (8),  emp_name char (Ten),  emp_cardid Char,  constraint pk_ EMP_ID primary KEY (emp_id),  constraint uk_emp_cardid Unique (emp_cardid)  ) on [primary]7.2.4 check constraint   The check constraint sets the check condition on the values in the input column or the entire table to limit the input values and ensure the data integrity of the database. You can set compliance checks on each column.   The syntax for defining CHECK constraints is as follows:  constrainT Constraint_name check [Not for replication]  (logical_expression)   The parameters are described below:  not for REPLICATION   Specifies that the check constraint does not occur when data copied from other tables is inserted into the table.  logical_expression  specifies that the logical condition expression returns a value of TRUE or false.   Example 7-6: Create an order form where the ordered quantity must be no less than 10.  create table   Orders ( order_id char (8),  p_id char (8),  p_name char (TEN),  quantity Smallint, constraint pk_order_id primary KEY (order_id), constraint chk_quantity check (quantity>=10), ) On [primary]  Note: You cannot make any constraints other than check constraints on a computed column. 7.2.5 DEFAULT Constraints   Default constraints specify the default values for the columns by defining the default values for the columns or by using the columns of the database's default object binding table. SQL Server recommends using default constraints instead of specifying the default values for the columns by using the default values defined. See the "Data integrity" section for ways to bind default constraints.   The syntax for defining default constraints is as follows:  constraint constraint_name default constant_expression [for column_name]  example 7-7:  constraint de_order_quantity default   order_quantity  Note: You cannot define a DEFAULT constraint when you create a table, but you can only add a default constraint to a table that has already been created. 7.2.6 column constraints and table constraints   for a database, constraints are divided into column constraints (columns Constraint) and table constraints (table Constraint). The   column constraint acts as part of the column definition only for the column itself. Table constraints, as part of the table definition, can be used for   multiple columns.   The following examples illustrate the columnThe difference between a constraint and a table constraint.   Example 7-8: create table products ( p_id char (8),  p_name char (TEN),  price money default 0.01,  Quantity smallint Check (quantity>=10),/* column constraint */ constraint pk_p_id primary key (P_ID, p_name)/* Table constraint */

Five types of constraints in the oracle--database