Chapter 06 - Creating Tables and Enforcing Data Integrity

Ky leksion 120 minutesh perfshin faqet 265 - 298 te librit te kursit.

Creating a table


-- Syntax of CREATE TABLE

CREATE TABLE 
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
    [ AS FileTable ]
    ( { <column_definition> | <computed_column_definition> 
        | <column_set_definition> | [ <table_constraint> ] [ ,...n ] } )
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | "default" } ] 
    [ { TEXTIMAGE_ON { filegroup | "default" } ] 
    [ FILESTREAM_ON { partition_scheme_name | filegroup 
        | "default" } ]
    [ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]

-- Sample table

CREATE TABLE Production.Categories(
categoryid INT IDENTITY(1,1) NOT NULL,
categoryname NVARCHAR(15) NOT NULL,
description NVARCHAR(200) NOT NULL)
GO
SELECT TOP (10) categoryname FROM Production.Categories;

-- Creating a schema

CREATE SCHEMA Production AUTHORIZATION dbo;
GO

-- The following statement moves the Production.Categories table to the Sales database schema:
ALTER SCHEMA Sales TRANSFER Production.Categories;
-- To move the table back, issue:
ALTER SCHEMA Production TRANSFER Sales.Categories;

-- Naming Tables and Columns 

-- For example, you could create a table as follows:
CREATE TABLE Production.[Yesterday's News]
-- Or you could write this way:
CREATE TABLE Production."Tomorrow's Schedule"

-- NULL and Default Values

-- Example of a default value
CREATE TABLE Production.Categories(
categoryid INT IDENTITY(1,1) NOT NULL,
categoryname NVARCHAR(15) NOT NULL,
description NVARCHAR(200) NOT NULL DEFAULT ('')
) ON [PRIMARY];
GO

-- The Identity Property and Sequence Numbers

-- The most common values for seed and increment are (1,1) as in the TSQL2012 Production.Categories table:
CREATE TABLE Production.Categories(
categoryid INT IDENTITY(1,1) NOT NULL,

-- Computed Columns

-- You could compute this in a SELECT statement:
SELECT TOP (10) orderid, productid, unitprice, qty, 
unitprice * qty AS initialcost -- expression
FROM Sales.OrderDetails;

-- You can take that expression, unitprice * qty AS initialcost, and embed it in the CREATE TABLE statement as a computed column. 
CREATE TABLE Sales.OrderDetails
(
  orderid   INT           NOT NULL,
  initialcost AS unitprice * qty -- computed column
);

-- Table Compression

-- The following command will add row-level compression to the Production.OrderDetails table as part of the CREATE TABLE statement:
CREATE TABLE Sales.OrderDetails
(
  orderid   INT           NOT NULL,
  ) 
WITH (DATA_COMPRESSION = ROW);
-- You can also ALTER a table to set its compression:
ALTER TABLE Sales.OrderDetails
REBUILD WITH (DATA_COMPRESSION = PAGE);

Using Constraints


-- Primary Key Constraints

-- For example, consider again the TSQL2012 table Production.Categories. This is how it is defined in the TSQL2012.SQL script:
CREATE TABLE Production.Categories
(
  categoryid   INT           NOT NULL IDENTITY,
  categoryname NVARCHAR(15)  NOT NULL,
  description  NVARCHAR(200) NOT NULL,
  CONSTRAINT PK_Categories PRIMARY KEY(categoryid)
);

--Another way of declaring a column as a primary key is to use the ALTER TABLE statement, which you could write as follows:
USE TSQL2012;
ALTER TABLE Production.Categories 
ADD CONSTRAINT PK_Categories PRIMARY KEY(categoryid);
GO

-- To list the primary key constraints in a database, you can query the sys.key_constraints table filtering on a type of PK:
SELECT * 
FROM sys.key_constraints 
WHERE type = 'PK';

-- Also you can find the unique index that SQL Server uses to enforce a primary key constraint by querying sys.indexes.
SELECT * 
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'Production.Categories', 'U') AND name = 'PK_Categories';

-- Unique Constraints

USE TSQL2012;
ALTER TABLE Production.Categories 
ADD CONSTRAINT UC_Categories UNIQUE (categoryname);
GO
SELECT * 
FROM sys.key_constraints 
WHERE type = 'UQ';

-- Foreign Key Constraints

USE TSQL2012
GO
ALTER TABLE Production.[Products]  WITH CHECK 
ADD  CONSTRAINT [FK_Products_Categories] FOREIGN KEY(categoryid)
REFERENCES Production.Categories (categoryid)
GO

SELECT P.[productname], C.categoryname
FROM Production.[Products] AS P
JOIN Production.Categories AS C
ON P.categoryid = C.categoryid;

SELECT * 
FROM sys.foreign_keys
WHERE name = 'FK_Products_Categories';

-- Check Constraints

ALTER TABLE Production.[Products]  WITH CHECK 
ADD  CONSTRAINT [CHK_Products_unitprice] 
CHECK  (unitprice>=0);
GO

SELECT *
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID(N'Production.Products', N'U');

-- Default Constraints

CREATE TABLE Production.Products
(
  productid    INT          NOT NULL IDENTITY,
  productname  NVARCHAR(40) NOT NULL,
  supplierid   INT          NOT NULL,
  categoryid   INT          NOT NULL,
  unitprice    MONEY        NOT NULL
    CONSTRAINT DFT_Products_unitprice DEFAULT(0),
  discontinued BIT          NOT NULL 
    CONSTRAINT DFT_Products_discontinued DEFAULT(0),
);

SELECT * 
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID(N'Production.Products', 'U');