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.