Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview Prep - SQL > Data Integrity Enforcement, Constraints and Indexes > Flashcards

Data Integrity Enforcement, Constraints and Indexes Flashcards

1
Q

Should you use rules or declarative constraints to enforce data integrity?

A
  • Declarative constraints
  • Rules are not as well suited for enforcing data integrity and will be deprecated in a future version of SQL Server

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

How are table constraints like views, tables, stored procedures, etc?

A

They are also considered database objects in SQL Server

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What are the 5 different types of constraints?

A
  1. Primary key constraint
  2. Unique constraint
  3. Foreign key constraint
  4. Check constraint
  5. Default constraint

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What does a UNIQUE CONSTRAINT allow you to do?

A

Enforce uniqueness of values in a column

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is automatically created when you create a UNIQUE CONSTRAINT?

A
  • A unique index with the same name as constraint.
  • The unique index is used to enforce the uniqueness of the column or combination of columns

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Can you have NULLS in a column you want to have a unique constraint?

A

Yes, but only one of the rows can be null

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Can PK constraints and Unique constraints be created on computed columns?

A

Yes

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is the max # of columns that can be the key columns of an index?

A

16

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the max size in bytes that the key column(s) of an index can have?

A

900 bytes

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How does a check constraint work?

A
  • You create an expression similar to a filter expression in a WHERE clause so SQL Server knows how to identify valid values and only let them in.
  • You can reference other columns in the same row and use built in functions.

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What do you need to take into account when making a CHECK constraint column that allows nulls?

A

Make sure that the CHECK CONSTRAINT expression you create will allow nulls to get through

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Can you customize an error message for if the CHECK CONSTRAINT fails?

A

No. You would have to use something like a trigger to do that

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Can you reference the previous value of a column in the CHECK constraint expression?

A

No. You would need to use a trigger for something like that.
Example: Unit price cannot be increased bby more than 25%

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What does a DEFAULT CONSTRAINT do?

A

Say a record is being inserted. For the insertion, column1 is null. DEFAULT CONSTRAINT basically says if the value is NULL instead assign this other value.

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What happens if you don’t supply a name for a DEFAULT CONSTRAINT?

A

SQL Server will give it a machine generated name.

SQL Server 70-461 08-02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Are all types of constraints objects?

A
  • Yes. Therefore their names must be unique across the entire database.
  • No two tables can have default constraints names the same thing

SQL Server 70-461 08-02

17
Q

What is the best way to enforce data integrity?

A

Create or declare constraints on base tables

SQL Server 70-461 08-02

18
Q

How are constraints applied to a table?

A

Use the CREATE TABLE or ALTER TABLE statements

SQL Server 70-461 08-02

19
Q

What is declarative data integrity?

A

When you embed methods of data integrity inside the definition of the table itself.

SQL Server 70-461 08-02

20
Q

How should you name a constraint?

A
  • Since a constraint is an object it must have a unique name when compared to other db objects.
  • Since it is an object but its scope is an individual table, you may want to use a naming convention that indicates type of constraint, table it applies to and key columns, if necessary.

SQL Server 70-461 08-02

21
Q

What is the standard form of the statement to add a PK?

A 
CONSTRAINT pk.categories PRIMARY KEY(categoryid)

pk.categories is the name you supply for PK constraints object
categorid is the column name that the PK is on

SQL Server 70-461 08-02

22
Q

What is the standard form of the statement to add a PK to an existing table?

A

ALTER TABLE Production.Categories
ADD Constraint PK_Categories
PRIMARY KEY (categoryid)

SQL Server 70-461 08-02

23
Q

What conditions must be met to create a PK on a column?

A
  1. The column cannot allow nulls
  2. If you are creating a PK on a column(s) in an already existing table, the values in that column must be unique
  3. There can only be one PK constraint on a table

If any of the conditions are not met, an attempt to create a PK will fail

SQL Server 70-461 08-02

24
Q

What is automatically created when you create a PK?

A

A unique index with the same name as the PK constraint

SQL Server 70-461 08-02

25
Q

How can you look up all PK constraints in a database?

A

SELECT *
FROM sys.key_constraints
WHERE type=’PK’

Just writing a sentence long enough that the text will left justify

SQL Server 70-461 08-02

26
Q

What is the general form of a statement to add a UNIQUE CONSTRAINT?

A

In a CREATE TABLE statement
CONSTRAINT UC_Categories
UNIQUE(categoryname)

In an ALTER TABLE statement
ADD CONSTRAINT UC_Categories
UNIQUE(categoryname)

SQL Server 70-461 08-02

27
Q

What is the general form of the statement to create a foreign key?

A 
CONSTRAINT FK_Products_Categories
FOREIGN KEY(categoryid)
REFERENCES Production.Categories(categoryid)
  • FK_Products_Categories is the name you want to give to FK constraint object
  • Foreign Key(categoryid) is the column in foreign key table that will be FK
  • Production.Categories(categoryid) is the column in the other table that the FK will relate to, usually a PK column

SQL Server 70-461 08-02

28
Q

What must the column in the other table that a FK relates to be?

A

The column must be one of
1. PK constraint
2. UNIQUE constraint
3. UNIQUE index

Ultimately, it must have a unique index and that can be implicitly created with 1. or 2. or explicitly created with 3.

SQL Server 70-461 08-02

29
Q

What does the WITH CHECK statement do?

A

ALTER TABLE Production.Products WITH CHECK
ADD CONSTRAINT FK_Products_Categories
FOREIGN KEY(categoryid)
REFERENCES Production.Categories(categoryid)

Makes sure that is there were any FK values not in the reference column of the other table, that the alter statement fails.

SQL Server 70-461 08-02

30
Q

What can increase performance when working with FK?

A
  • Creating a nonclustered index on the FK. Unlike PK columns, an index is not automatically created when a FK is created.
  • Joins may be resolved faster if FK also has an index

SQL Server 70-461 08-02

31
Q

General form of check constraint

A

CONSTRAINT constraint_name
CHECK expression

Expression Example: (unitprice>=0)

SQL Server 70-461 08-02

32
Q

How does WITH CHECK work?

A

When using the ALTER TABLE statemen, WITH CHECK makes sure that any records that already exist in the table are put through the constraint you are newly creating. The ALTER TABLE statement will fail if there is a violation.

SQL Server 70-461 08-02

33
Q

What is the advantage of having a constraint in the table vs at the application level?

A

If it is at the table level it will always be enforced. If it is at the application level, there is always a chance that data might get into the table that shoudn’t.

SQL Server 70-461 08-02

34
Q

Why use a check constraint instead of a trigger?

A

It may perform better.

SQL Server 70-461 08-02

35
Q

How can you get a list of CHECK CONSTRAINTS

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

SQL Server 70-461 08-02

36
Q

Do you apply a DEFAULT CONSTRAINT in CREATE TABLE or ALTER TABLE statement?

A

It can be created in either but is more commonly applied during CREATE TABLE

SQL Server 70-461 08-02

37
Q

In the CREATE TABLE statement, where is the DEFAULT CONSTRAINT applied?

A
  • One place it can be applied is after a columns data type
  • Example:
    unitprice MONEY NOT NULL
    CONSTRAINT DFT_PRODUCTS_unitprice
    DEFAULT(0),

SQL Server 70-461 08-02

38
Q

General form of DEFAULT CONSTRAINT

A 
CONSTRAINT DFT_Products_discontinued DEFAULT(0)

DFT_Products_discontinued constraint name

SQL Server 70-461 08-02

39
Q

How can you get a list of DEFAULT CONSTRAINTS?

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

SQL Server 70-461 08-02

Interview Prep - SQL (26 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions