SQL Server Constraints

SQL Server constraints used for enforcing business rules into database table. Also known as Data Integrity. Used to validate data

Syntax:
columnname datatype constraint_name

Examples:

CREATE TABLE [dbo].[emp](
[empname] [varchar](20) NULL,
[empno] [numeric](18, 0) NOT NULL,
CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED
[job] [varchar](30) NULL,
[joindate] [date] NULL,
[salary] [money] NULL,
[deptno] [nchar](10) NULL,
[location] [nchar](10) NULL
)

Primary Key:

  • This is nothing but collection of one or more columns used to identify a record.
  • A column declared with Primary key does not allow duplicates and null values.
  • A table allows only one Primary key
  • Numeric columns are preferred as Primary keys than string columns
  • A Primary key may be simple or composite Primary key
  • Primary key constructed with multiple columns is called as composite primary key. Composite primary key always defined after all column definitions.

Unique:
Unique key constraint is used to identify a row. Unique constraint columns does not allow duplicate values. But, single null value allowed in a column.
Example
CREATE TABLE emp
(
empno int NOT NULL,
CONSTRAINT AK_empno UNIQUE(empno)
)

Not Null:
A column declared with not null int that column, null values are not allowed.

CHECK
Check constraints are used to validate data based on a condition

Example:
CREATE TABLE emp
(
empno int NOT NULL,
salary money check(salary>2500)
)

Foreign Key
Foreign key is nothing but a column referencing values from another table primary column(or unique).
Foreign key column name and datatype to be same as primary key column.
A table can have maximum 253 253 foreign key columns.
Foreign key column allows duplicates and null values by default.

Syntax:
create table product
(
product_id uniqueidentifier primary key,
product_batch_id uniqueidentifier not null,
product_desc varchar(1024) not null,
product_value decimal,
foreign key ( product_batch_id ) references exams (batch_id)
)

Powered by k2schools