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');

Ushtrime: 

1. Krijoni skemen Course
2. Krijoni nje tabele Subjects e cila te permbaje id identitet dhe description 
3. Krijoni nje tabele Students e cila te permbaje id identitet, firstname, lastname, gender, birthdate
4. Krijoni nje tabele Interest  e cila te permbaje id identitet, id studenti dhe id subject ku referohet tabela students dhe tabela Subjects respektivisht.
5. Per tabelat duke perdorur Edit top 200 rows (klikoni ne IDE SqlServer 2012 tek emri i tabeles me butonin e djathte te mouse) shtoni perkatesisht 3,5,10,rreshta me informacion.
6. Ndertoni nje Query te pershtatshme select per te shfaqur Subjekt, Emer Studenti, Mbiemer Studenti, te renditur sipas subjektit.
7. Ndertoni nje Query te pershtatshme select per te shfaqur Subjekt, numer studentesh qe kane interes ne lidhje me ate subjekt.