Data integrity (Integrity) Notes

Because the database stores data consistently, tables in the database require methods to validate various data types. It is not limited to data types, there are unique values, ranges of values, or the values of a column match the columns in another table.

When you define a table, it uses these data validation methods. This is called declaring data integrity. That's what we call table constraints.

 UsetempdbGOCREATE TABLEs (SidVARCHAR( -), snameVARCHAR( -), SsexVARCHAR(2)CHECK(Ssex= 'male'                              ORSsex= 'female' )                      DEFAULT 'male', SageINT CHECK(Sagebetween 0  and  -), SclassVARCHAR( -)UNIQUE ,      CONSTRAINTpk_sPRIMARY KEY(SID, Sclass))CREATE TABLET (teacherVARCHAR( -)PRIMARY KEY, SidVARCHAR( -) not NULL, SclassVARCHAR( -) not NULL, NumINT ,      FOREIGN KEY(SID, Sclass)REFERENCESs (SID, Sclass))

PRIMARY KEY constraint Primary key Constraints

PRIMARY KEY = UNIQUE constraint + NOT NULL constraint

When you create a PRIMARY key constraint, the database automatically creates a unique index, which defaults to a clustered index

Create method One

--Primary Key ConstraintsCREATE TABLEProduction.categories (CategoryIDINT            not NULL IDENTITY, CategoryNameNVARCHAR( the) not NULL, DescriptionNVARCHAR( $) not NULL,  CONSTRAINTPk_categoriesPRIMARY KEY(CategoryID));

Create method Two

 Use TSQL2012; ALTER TABLE production.categories      ADD CONSTRAINT PRIMARY KEY (CategoryID); GO

List primary KEY constraints in a database

--  to list the primary key constraints in a database, you can query the Sys.key_constraints table filtering on a type of PK:SELECT*fromWHERE='PK' ;

Uniqueness Constraint Unique Constraints

There can be only one behavior in null,oracle where multiple row values can be null.

When you create a unique key constraint, the database automatically creates a unique index, which defaults to a nonclustered index

In terms of ensuring data uniqueness, the unique index, the unique constraint is not different, should you use a constraint or an index?

Constraint definitions typically occur during the design phase of the database logical structure, where the table structure is defined, and the index definition usually occurs during the database physical structure design/query optimization phase.

Functionally, unique constraints and unique indexes do not differ, but in database maintenance is not the same, for unique constraints can be replaced with a unique index, to facilitate maintenance, but the primary key constraints are not replaced.

ALTER TABLE production.categories      ADD CONSTRAINT UNIQUE (CategoryName); GO

List UNIQUE constraints in a database

SELECT *  from  WHERE='UQ';

Foreign key Foreign Key Constraints

Create

ALTER TABLE Production. [Products]   with CHECK     ADD  CONSTRAINT [fk_products_categories] FOREIGN KEY (CategoryID)     REFERENCES Production.categories (CategoryID)

Find foreign keys

SELECT *  from Sys.foreign_keys WHERE = ' fk_products_categories ';

Delete foreign key

ALTER TABLE DROP CONSTRAINT Fk_products_categories;

Check constraint check Constraints

Create

 alter  table  production.products Span style= "color: #0000ff;" >with  check  add  constraint   Chk_products_unitprice   CHECK  (Unitprice>=  0   go  

ALTER TABLE  ADDINTNULLCONSTRAINTCHECKlike'  [0-9][0-9][0-9][0-9][0-9]');

Find CHECK constraints

SELECT *  from sys.check_constraints WHERE = object_id (n'production.products', n'U');

Default constraint Defaults Constraints

Create

CREATE TABLEproduction.products (ProductIDINT           not NULL IDENTITY, ProductNameNVARCHAR( +) not NULL, SupplierIDINT           not NULL, CategoryIDINT           not NULL, UnitPrice Money         not NULL    CONSTRAINTDft_products_unitpriceDEFAULT(0), discontinuedBIT           not NULL     CONSTRAINTdft_products_discontinuedDEFAULT(0),);

Find

SELECT *  from sys.default_constraints WHERE = object_id (N'production.products'U');

Enable and disable constraint checking

ALTER TABLE NOCHECK CONSTRAINT Chk_price; ALTER TABLE CHECK CONSTRAINT Chk_price;

Check for constraints in SQL Server

--easiest-to-check for the existence of a constraint (and then do something such as drop it if it exists) are to use The object_id () function ...IF object_id('Ck_constraintname','C') is  not NULL     ALTER TABLEDbo.[TableName] DROP CONSTRAINTCk_constraintname--object_id can used without the second parameter (' C ' for check constraints only) and that may also work, but if you R constraint name matches the name of the other objects in the database you may get unexpected results. IF object_id('Ck_constraintname') is  not NULL     ALTER TABLEDbo.[TableName] DROP CONSTRAINTCk_constraintname--object_id can also is used with other "constraints" such as Foreign key constraints or Primary Key constraints, etc. F Or best results, always include the appropriate object type as the second parameter for the OBJECT_ID function:--Constraint Object Types:--• C = CHECK constraint--• D = DEFAULT (constraint or stand-alone)--• F = FOREIGN KEY constraint--PK = PRIMARY KEY constraint--• R = Rule (Old-style, stand-alone)--UQ = UNIQUE constraint

Reference articles

09. Constraint-to-index linkages

Data integrity (Integrity) Notes