A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures the uniqueness and integrity of the data.
What is the purpose of a primary key?
The primary key serves as a unique identifier for each row in a table. It enforces data integrity and provides a way to uniquely identify and reference a specific row.
How do you define a primary key in SQL Server?
A primary key can be defined when creating a table using the "PRIMARY KEY" constraint, or it can be added to an existing table using the "ALTER TABLE" statement.
Can a table have multiple primary keys?
No, a table can have only one primary key. However, a primary key can consist of multiple columns (composite key).
What is a unique key in SQL Server?
A unique key is similar to a primary key in that it ensures the uniqueness of data. However, unlike a primary key, a unique key allows NULL values.
What is the difference between a primary key and a unique key?
A primary key is used to uniquely identify each row in a table and does not allow NULL values, while a unique key also ensures uniqueness but allows NULL values.
How do you define a unique key in SQL Server?
A unique key can be defined when creating a table using the "UNIQUE" constraint, or it can be added to an existing table using the "ALTER TABLE" statement.
Can a table have multiple unique keys?
Yes, a table can have multiple unique keys. Each unique key will enforce uniqueness independently.
What is a foreign key in SQL Server?
A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables.
What is the purpose of a foreign key?
A foreign key is used to enforce referential integrity between related tables. It ensures that the values in the foreign key column(s) match the values in the referenced primary key column(s).
How do you define a foreign key in SQL Server?
A foreign key can be defined when creating a table using the "FOREIGN KEY" constraint, or it can be added to an existing table using the "ALTER TABLE" statement.
Can a table have multiple foreign keys?
Yes, a table can have multiple foreign keys. Each foreign key establishes a separate relationship with a different table.
What are the types of relationships that can be established using foreign keys?
The types of relationships are: one-to-one, one-to-many, and many-to-many.
What is a composite key?
A composite key is a primary key or a unique key that consists of more than one column. It provides a way to uniquely identify a row using a combination of multiple columns.
Why would you use a composite key?
A composite key is used when a single column is not sufficient to uniquely identify a row. It allows for more precise and specific identification of rows.
Can a table have multiple composite keys?
No, a table can have only one primary key or unique key, which can be a composite key. However, multiple composite keys are not allowed.
What is the difference between a primary key and a composite key?
A primary key is a unique identifier for a row in a table and can be a single column or a combination of columns. A composite key, on the other hand, is specifically a key that consists of multiple columns.
How do you create a composite key in SQL Server?
To create a composite key, you need to specify multiple columns when defining the primary key or unique key constraint.
What is the purpose of an index in SQL Server?
An index is a database structure that improves the speed of data retrieval operations on a table. It allows for faster searching, sorting, and filtering of data.
Can you create an index on a primary key or unique key?
Yes, a primary key or unique key automatically creates a clustered index by default in SQL Server. However, you can also create additional non-clustered indexes on these keys for further optimization.
What is the difference between a clustered and a non-clustered index?
A clustered index determines the physical order of data in a table and is typically created on the primary key. A non-clustered index is a separate structure that points to the data in the table and can be created on any column(s) in a table.
What is a surrogate key?
A surrogate key is an artificial primary key assigned to a row in a table, typically as an auto-incrementing integer value. It is used when there is no suitable natural key available.
What is a natural key?
A natural key is a primary key that is derived from the data itself, such as a person's social security number or a product's barcode. It is based on real-world attributes or business meaning.
What is the difference between a primary key and a surrogate key?
A primary key is based on real-world attributes or business meaning and can be either a natural key or a composite key. A surrogate key, on the other hand, is an artificial key generated solely for the purpose of identifying a row in a table.
What is referential integrity?
Referential integrity is a database concept that ensures that relationships between tables are consistent and valid. It guarantees that foreign key values always correspond to the primary key values they reference.
What happens when a foreign key constraint is violated?
If a foreign key constraint is violated, such as when a foreign key value does not exist in the referenced primary key column, the database engine will reject the operation and raise an error.
Can you have a foreign key that references multiple tables?
No, a foreign key can only reference a single table. However, you can establish multiple foreign keys in a table to reference different tables.
What is the "CASCADE" option in a foreign key constraint?
The "CASCADE" option allows for automatic cascading of changes or deletions to related records. For example, if a row in the referenced table is deleted, all related rows in the referencing table will also be deleted.
What is the "ON DELETE SET NULL" option in a foreign key constraint?
The "ON DELETE SET NULL" option sets the foreign key value to NULL when the referenced row is deleted. This is useful when you want to allow orphaned records in the referencing table.
What is the "ON UPDATE CASCADE" option in a foreign key constraint?
The "ON UPDATE CASCADE" option ensures that any updates to the primary key values in the referenced table are automatically propagated to the foreign key values in the referencing table.
What is the difference between "DELETE" and "TRUNCATE" in SQL Server?
"DELETE" is a DML (Data Manipulation Language) statement that removes specific rows from a table, while "TRUNCATE" is a DDL (Data Definition Language) statement that removes all rows from a table and resets identity columns.
Can you have a primary key or unique key with duplicate values?
No, a primary key and a unique key must have unique values. Duplicate values are not allowed.
Can you have a foreign key with NULL values?
Yes, a foreign key can have NULL values unless it is explicitly defined as "NOT NULL" when creating the table.
What is the purpose of the "IDENTITY" property in SQL Server?
The "IDENTITY" property is used to automatically generate a unique value for a column, typically used for surrogate keys. It is often combined with the primary key column.
What is the purpose of the "SCOPE_IDENTITY()" function in SQL Server?
The "SCOPE_IDENTITY()" function returns the last identity value generated in the current scope. It is commonly used to retrieve the value of an identity column after an insert operation.
Can a primary key or unique key be a foreign key in another table?
Yes, a primary key or unique key can be referenced by a foreign key in another table. This establishes a relationship between the two tables.
What is the purpose of the "CHECK" constraint in SQL Server?
The "CHECK" constraint is used to enforce domain integrity by defining a condition that must be met for the data in a column.
Can you create a primary key or unique key on a nullable column?
Yes, a primary key or unique key can include nullable columns. However, only one NULL value is allowed in a unique key column.
What is the purpose of the "NOT NULL" constraint in SQL Server?
The "NOT NULL" constraint is used to ensure that a column does not contain NULL values. It enforces data integrity by requiring a value to be present.
Can you have duplicate values in a clustered index?
No, a clustered index determines the physical order of data in a table and enforces uniqueness. Duplicate values are not allowed.
What is the purpose of the "WITH NOCHECK" option when creating a foreign key constraint?
The "WITH NOCHECK" option allows you to create a foreign key constraint without checking the existing data for validity. It is useful when creating relationships on existing data that might violate the constraint.
What is the purpose of the "WITH CHECK" option when altering a foreign key constraint?
The "WITH CHECK" option ensures that the existing data in the referencing table is checked for validity against the foreign key constraint. It is useful when you want to verify the integrity of the data.
What is the purpose of the "DISABLE TRIGGER" statement in SQL Server?
The "DISABLE TRIGGER" statement is used to temporarily disable one or more triggers on a table. This can be useful when you need to perform data modifications without triggering the associated triggers.
What is the purpose of the "ENABLE TRIGGER" statement in SQL Server?
The "ENABLE TRIGGER" statement is used to re-enable one or more triggers on a table after they have been disabled. This allows the triggers to be activated again.
Can you drop a primary key or unique key constraint in SQL Server?
Yes, you can drop a primary key or unique key constraint using the "ALTER TABLE" statement with the "DROP CONSTRAINT" clause.
Can you drop a foreign key constraint in SQL Server?
Yes, you can drop a foreign key constraint using the "ALTER TABLE" statement with the "DROP CONSTRAINT" clause.
Can you create a primary key or unique key constraint on an existing table in SQL Server?
Yes, you can add a primary key or unique key constraint to an existing table using the
"ALTER TABLE" statement with the "ADD CONSTRAINT" clause.
Can you create a foreign key constraint on an existing table in SQL Server?
Yes, you can add a foreign key constraint to an existing table using the "ALTER TABLE" statement with the "ADD CONSTRAINT" clause.
What is the purpose of the "CASCADE CONSTRAINTS" option when dropping a table in SQL Server?
The "CASCADE CONSTRAINTS" option automatically drops all the foreign key constraints that reference the table being dropped. It ensures that the dependencies are handled automatically.
What is the purpose of the "ALTER TABLE ... NOCHECK CONSTRAINT" statement in SQL Server?
The "ALTER TABLE ... NOCHECK CONSTRAINT" statement temporarily disables all the foreign key constraints on a table. This allows you to perform data modifications without enforcing the integrity constraints.
No comments:
Post a Comment