[1] Create Tables, Enforcing Integrity

Question 1

Give 5 variations of the standard Table.

Answer 1

1. Temp tables: a tables that exist only in tempdb and last as long as a session or scope referencing it.
2. Table variables: tables that store data only for a batch.
3. Views: a query on a table or multiple tables.
4. Indexed views: views that update when the base table is updated.
5. Derived tables: CTEs. Sub-queries referenced like tables.

Question 2

What is a database schema and what is it’s primary purpose?

Answer 2

A database schema is a namespace that groups tables together.

Question 3

What are the 4 built-in database schema and what do they store?

Answer 3

1. dbo: default schema for saving new objects for owners/admin users.
2. guest schema: stores info for a guest user
   INFORMATION_SCHEMA: provides ANSI access to metadata.
3. system database schema: for system objects like tables/views

Question 4

What is the syntax to move a table from one schema to another?

Answer 4

ALTER SCHEMA schemaTo TRANSFER schemaCurrent.table

Question 5

What are the two types of identifiers and how do they differ?

Answer 5

Regular: names that do not require delimiters
Delimited: names that require [] or “”

Question 6

What does the SET QUOTED_IDENTIFIER setting do?

Answer 6

When ON, it allows for quotations to be used for delimited identifiers

Question 7

What are the 4 types of constraints when creating/altering a table?

Answer 7

1. DEFAULT
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK

Question 8

Syntax a primary key on column “ID”

Answer 8

CONSTRAINT PK_tablename PRIMARY KEY(ID)

Question 9

What are the 3 main requirements for creating a primary key out of a column?

Answer 9

1. Column must not allow nulls
2. Column must have no duplicates
3. Column is the only primary key in the table

Question 10

Syntax to check for primary keys on a table

Answer 10

SELECT *
from sys.key_constraints
WERE type = ‘PK’

Question 11

What is the difference between a primary key and UNIQUE key columns?

Answer 11

A unique key column can take 1 null value. Primary key columns cannot have any nulls

Question 12

Syntax to add uniqueness to column “ID”

Answer 12

ALTER TABLE schema.tablename

ADD CONSTRAINT UC_tablename UNIQUE (ID)

Question 13

Syntax to check for unique keys on a table

Answer 13

SELECT *
from sys.key_constraints
WERE type = ‘UQ’

Question 14

What is the definition of a foreign key?

Answer 14

A (combination of) column(s) in one table that serve as a link to look up data in another (lookup) table.

Question 15

Syntax to add a foreign key to column “ID”

Answer 15

ALTER TABLE schema.tablename1
ADD CONSTRAINT FK_tablename FOREIGN KEY (ID)
REFERENCES tablename2 (ID)

Question 16

Syntax to check for adding constraint check on a table

Answer 16

ALTER TABLE schema.table WITH CHECK
ADD CONSTRAINT CHK_name
CHECK (col1 < 5)

Question 17

Syntax to check for check constraints on a table

Answer 17

SELECT *
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID(N’schema.table’, N’U’)

Question 18

What is the purpose of the default constraint?

Answer 18

To create an alternative value in a column that does not allow nulls.

Question 19

Syntax to check for default constraints on a table

Answer 19

SELECT *
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID(N’schema.table’, N’U’)

Question 20

What syntax allows you to move a table from one schema to another?

Answer 20

ALTER SCHEMA TRANSFER

Question 21

What is the syntax that allows you to insert a row in an identity column?

Answer 21

SET IDENTITY_INSERT tablename ON/OFF