SQL Tutorial - Create Table with Constraints

Опубликовано: 28 Май 2017
на канале: BeardedDev

15,657

159

This video tutorial will show you how to create a table with constraints in Microsoft SQL Server. The video covers, data types of columns, NOT NULL constraints, PRIMARY KEY constraints, FOREIGN KEY constraints, CHECK constraints and DEFAULT constraints.

Data types enforce limits to the what can inserted in to tables and constraints further build on this, for example if you wanted to add a column called age, the values will usually range from 0 - 100 so we could choose a data type of TINYINT which allows values of up to 255 but this means if you enter 255 this is still a valid age. We want to control this even further so we can add a CHECK constraint that will only allow values from 0 - 100. Check out the video for how to add this constraint.

CREATE TABLE dbo.[Login]
(
Username VARCHAR(30) NOT NULL
CONSTRAINT PK_Login_Username PRIMARY KEY (Username)
, [Password] VARCHAR(30) NOT NULL
, LastUpdate DATETIME2 NOT NULL
CONSTRAINT DF_Login_LastUpdate DEFAULT (CURRENT_TIMESTAMP)
, [Status] CHAR(10) NOT NULL
CONSTRAINT CK_Login_Status CHECK ([Status] IN ('Active', 'Inactive'))
, CustomerID INT
CONSTRAINT FK_Login_CustomerId FOREIGN KEY (CustomerId) REFERENCES Customers (CustomerId)
)