T-SQL Tutorial

Constraints T-SQL Tutorial


In the Constraints sections you can learn how to create a SQL Primary Key Constraint or add a Foreign Key to a table. Also you can learn how to use SQL Server commands to enable or disable keys.

Primary Key Constraints

The primary key consists of a column or a combination of several columns.
The columns of the primary key contain unique values that identify each row in a table.
The maximum number of columns allowed to compose a primary key is 16 columns and a total key length of 900 bytes.
A table can contain only one primary key constraint.
PRIMARY KEY constraints do not allow for the value NULL.

USE tempdb;
GO
CREATE TABLE Students(
id int NOT NULL,
name varchar(300),
birthday date,
city varchar(300),
CONSTRAINT PK_STU_ID PRIMARY KEY (id));
GO

Foreign Key Constraints

A foreign key is a constraint is a relationship between two tables and used to enforce data integrity. Foreign Key constraints can reference only tables within the same database on the same server.

USE tempdb;
GO
CREATE TABLE students(
id int NOT NULL,
name varchar(300),
birthday date,
city varchar(300),
CONSTRAINT PK_STU_ID PRIMARY KEY (id));
GO
CREATE TABLE courses(
id int NOT NULL,
name varchar(300),
price int,
CONSTRAINT PK_COURSE_ID PRIMARY KEY (id));
GO
CREATE TABLE register_course(
id int NOT NULL,
student_id int,
course_id int,
CONSTRAINT PK_REG_C_ID PRIMARY KEY (id),
CONSTRAINT FK_STU_ID FOREIGN KEY (student_id) REFERENCES Students (id),
CONSTRAINT FK_COURSE_ID FOREIGN KEY (course_id) REFERENCES courses (id));
GO





UNIQUE Constraints

Unique constraints are used to prevent duplicate values from being inserted into specific columns in a table.
UNIQUE constraints allow for the value NULL.

USE tempdb;
GO
CREATE TABLE register_course_log(
log_id int NOT NULL,
log_date date,
CONSTRAINT UK_RCL_LOG_ID UNIQUE(log_id));
GO

CHECK Constraints

CHECK constraints specify the data values that are allowed in one or more columns.
CHECK constraints determine the valid values from a logical expression.

USE tempdb;
GO
ALTER TABLE courses
ADD CONSTRAINT CHK_COURSE_PRICE
CHECK (price > 20 AND price < 100);
GO

Examples