SQL for Beginners : Keys and Constraints

Keys and Constraints are rules that define what data values are allowed in certain data columns. They are an important database concept and are part of a database's schema definition. Keys are also used to create relationship among different database tables or views. SQL constraints are used to specify rules for the data in a table.

• Primary Key— is a key used to identified a table and to relation one table with another table. A unique value in a table can be use as a Primary Key. Is created when the table structures are being set.

	    CREATE TABLE [dbo].[Example](
			[Id] [int] NOT NULL,
			[Description] [varchar](50) NULL,
			[Date] [datetime] NULL,
	 	CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED 
		(
			[Id] ASC
		)
		) ON [PRIMARY]

PK Definition

Primary key can have IDENTITY specified 'Yes' or 'No'. Identity column of a table is a column whose value increases automatically. The value in an identity column is created by the server that's mean this can not be specified by a user. Identity column can be used to uniquely identify the rows in the table.

Autoincrement identity

Setting identity

Right click -> Design -> Click the PK column -> Go to Column properties -> Identity specification -> In the for default value 'No', make double click -> Set the increment and Seed values -> Ctrl S (or Save).

• Unique Key— is a key to stablish that the values inserted in a column of a table has to be distinct to the others values in that column. The UNIQUE constraint ensures that all values in a column are different. However you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

ALTER TABLE Example
ADD UNIQUE (IdentityCard);

If I try to insert two identical values in the IdentityCard column, Sql management studio will trow the error showed in the before picture.

• Foreign Key— is the primary key in a table and is used to make a relation with another table.

ALTER TABLE [dbo].[Example] ADD  CONSTRAINT [FK_Example_User] 
FOREIGN KEY([Id])
REFERENCES [dbo].[UserExample] ([Id])

• Composite key— A composite key is a specific type of primary key which uses the contents of two or more fields from a table to create a unique value. Combining two or more columns: Id, User, User_Example. You can create a composite primary key or a composite unique key.

	ALTER TABLE Example ADD PRIMARY KEY (Id, IdentityCard);

• Index (Clustered/Non-clustered)

It is a row identifier that references in each column within a table.

An index is like an index in a book that help you to find a topic looking it in the index book and then you don’t have to go page by page to find the topic. An index can improve the performance in a query. There are two types of indexes, Index Clustered and Index Non-Clustered.

• Index clustered: has all the information of each value within the table and a table can only have one index clustered.

ALTER TABLE [dbo].[Example] ADD  CONSTRAINT [PK_Example] PRIMARY KEY 							   CLUSTERED 
(
	[Id] ASC
)

• Index Non-clustered: has a reference of the information of each value within the table and a table can have more than one indexes non-clustered. An index clustered can have a include value. An include is when other columns are in the no clustered index specified.

CREATE NONCLUSTERED INDEX [NonCluesteredIdx] ON [dbo].				 [Example]
(
	[Description] ASC,
	[Date] ASC,
	[User_Example] ASC
)

SQL server use a B3 structure for the index obtaining with this a better improvement in the performances of the queries when these are logically structures.

*Set of date used :

WorldCities.txt

Download TXT • 11KB