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. The SQL Server constraints are Primary Key, Foreign Key, UNIQUE, CHECK and NOT NULL.
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