DB2 Key constraint operation

Today encountered a problem, want to delete a table in a column of the only constraint, Google a bit, search out many, find can use, write down to summarize as follows.

The command is as follows

Select Constname, TabName, ColName, colseq from Syscat.keycoluse where tabname= ' TableName '

ALTER TABLE db2admin. Bm_servers DROP UNIQUE <constname>;

The first line finds the constname corresponding to the row, and the second line removes it from the table.

First, record the relevant commands and the table "reprint"

catalog view	View columns	Describe	Query instance
SYSCAT. CHECKS		Check the constraint for each table contains one row of records	DB2 Select Constname, tabname, text from Syscat.checks
SYSCAT. Colchecks		Contains a row of records for each column referenced by the table check constraint	DB2 Select Constname, TabName, colname, usage from syscat.colchecks
SYSCAT. COLUMNS	NULLS	Indicates whether a column can be empty (Y) or non-nullable (N)	DB2 Select TabName, ColName, nulls from syscat.columns where Tabschema = ' Melnyk ' and nulls = ' N '
SYSCAT. Constdep		Contains one row of records for each dependency on constraints on some other object	DB2 Select Constname, TabName, Btype, bname from SYSCAT.CONSTDEP
SYSCAT. INDEXES		Include one row of records for each index	DB2 Select TabName, Uniquerule, Made_unique, system_required from syscat.indexes where Tabschema = ' Melnyk '
SYSCAT. Keycoluse	Each column contained in a key defined for a unique, primary key, or FOREIGN KEY constraint contains one row of records	DB2 Select Constname, TabName, ColName, colseq from Syscat.keycoluse
SYSCAT. REFERENCES	Include one row of records for each reference constraint	DB2 Select Constname, TabName, Refkeyname, Reftabname, ColCount, DeleteRule, updaterule from Syscat.references
SYSCAT. Tabconst	Include a row of records for each unique (U), primary key (P), foreign Key (F), or table check (K) constraint	DB2 Select Constname, TabName, type from Syscat.tabconst
SYSCAT. TABLES	Parents	The number of parent tables for the table (the number of reference constraints in which the table acts as a child table)	DB2 "Select TabName, parents from Syscat.tables where parents > 0"
SYSCAT. TABLES	Children	The number of child tables in the table in which the table serves as the number of reference constraints for the parent table	DB2 "Select TabName, children from Syscat.tables where children > 0"
SYSCAT. TABLES	Selfrefs	The number of self-referential referential constraints for the table in which both serve as both the parent table and the number of reference constraints for the child table	DB2 "Select TabName, selfrefs from syscat.tables where selfrefs > 0"
SYSCAT. TABLES	Keyunique	The number of unique constraints (except primary keys) defined on the table	DB2 "Select TabName, Keyunique from syscat.tables where Keyunique > 0"
SYSCAT. TABLES	Checkcount	The number of check constraints defined on the table	DB2 "Select TabName, Checkcount from syscat.tables where Checkcount > 0"

For more information, please see http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0401melnyk/

DB2 Official Document: Http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.kc.doc/welcome.html?lang=zh

Know it, know its why, then the following is to discuss these are God horse ah? From the above documents, of course.

There are five kinds of constraints for DB2:

• A not NULL constraint is a rule that prevents null values from being entered in one or more columns of a table.

• A unique constraint , also known as a unique key constraint , is a rule that prevents duplicate values from appearing in one or more columns of a table. Unique and primary keys are supported unique constraints. For example, you can define a unique constraint on the vendor identity in the Vendor table to ensure that the same vendor identity is not specified for two vendors.

• A PRIMARY KEY constraint is a combination of a column or column that has the same properties as a unique constraint. You can use primary key and foreign key constraints to define relationships between tables.

• A foreign KEY constraint (also known as a referential constraint or referential integrity constraint ) is a logical rule about the values in one or more columns in one or more tables. For example, a group of tables shares information about a company's vendors. The name of the vendor may sometimes change. A referential constraint can be defined, and the identity of the vendor in the Declaration table must match the vendor's identity in the vendor information. This constraint prevents insert, UPDATE, or delete operations that could cause loss of vendor information.

• (table) Check constraints (also known as CHECK constraints ) to set restrictions on the data added to a particular table. For example, a table check constraint ensures that an employee's salary level is at least $20000 whenever salary data is added or updated in a table containing personal information.

Create NOT NULL:

CREATE TABLE EMPLOYEES (...
Emergency_phone CHAR () not NULL,
. . .
);

Modified:

DB2 "ALTER TABLE tabname alter COLNAME drop NOT NULL"
DB2 "ALTER TABLE T01 ALTER COLNAME set NOT NULL"

Create and delete unique constraints:

DB2 "ALTER TABLE tabname add unique (colname)"

DB2 "ALTER TABLE tabname drop unique Constname"

To create a PRIMARY KEY constraint:

DB2 "ALTER TABLE staff add primary key (ID)"

To create a FOREIGN KEY constraint:

DB2 ALTER TABLE Project Add foreign key (respemp) references employee on DELETE Cascade

Table CHECK Constraints:

DB2 ALTER TABLE employee ADD constraint phoneno_length check (length (RTrim (phoneno)) = 4)

DB2 Key constraint operation