DBT-ANALYTICS-ENGINEERING practice question 9 of 35
Which of the following is the correct syntax for creating a new table named 'orders' with two columns: 'id' as an integer primary key and 'customer_id' as an integer foreign key referencing 'customers' table?
Full set: 409 questionsDomain Modeling
This option presents a syntactically valid SQL statement for creating the 'orders' table with the specified constraints. The id INT PRIMARY KEY clause correctly defines the primary key inline. The customerid INT FOREIGN KEY REFERENCES customers(id) clause correctly defines an inline foreign key constraint. This specific syntax, including the FOREIGN KEY keyword directly in the column definition, is valid in certain SQL dialects like Transact-SQL (used by Microsoft SQL Server). Although other dialects might omit the FOREIGN KEY keyword for inline definitions, this syntax is correct and the other options contain definitive errors.
A. This option incorrectly places a comma between the FOREIGN KEY and REFERENCES keywords, which is a syntax error and will cause the statement to fail.
C. This option attempts to define an out-of-line (table-level) constraint but fails to enclose the foreign key column name (customerid) in parentheses, which is required.
D. This option correctly uses the inline REFERENCES clause but then adds a dangling, incomplete FOREIGN KEY keyword at the end, which is syntactically invalid.
1. Microsoft Transact-SQL Documentation: The official documentation for CREATE TABLE in T-SQL shows the syntax for a column-level foreign key constraint as: [ FOREIGN KEY ] REFERENCES [ schemaname . ] referencedtablename [ ( refcolumn ) ]. This confirms that the FOREIGN KEY keyword is optional but valid in an inline definition
making option B correct.
Source: Microsoft
CREATE TABLE (Transact-SQL)
SQL Server Documentation
"columnconstraint" syntax section.
2. PostgreSQL Documentation: The PostgreSQL documentation illustrates the standard syntax for an out-of-line foreign key constraint
which is FOREIGN KEY ( columnname [
... ] ) REFERENCES reftable [ ( refcolumn [
... ] ) ]. This highlights the missing parentheses error in option C.
Source: PostgreSQL
PostgreSQL 16 Documentation
Section 5.4.5. "Foreign Keys".
3. University Courseware (Stanford): Course materials for database systems typically distinguish between attribute-level (inline) and table-level (out-of-line) constraints. The standard out-of-line syntax requires parentheses around the key
confirming that option C is incorrect.
Source: Stanford University
CS 145 Introduction to Databases
"SQL Constraints" lecture notes. (Illustrates standard syntax FOREIGN KEY () REFERENCES ()).
Keep practicing
Take the free DBT-ANALYTICS-ENGINEERING practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other DBT-ANALYTICS-ENGINEERING practice questions
- Question 1What is the purpose of the --resource-type argument in the dbt ls command?
- Question 2Select the valid methods for configuring incremental models in dbt. (Select all that appl…
- Question 3When fixing a dbt model issue, it is acceptable to skip the testing process in a separate…
- Question 4You want to run tests on only the models that have been modified since your last dbt run.…
- Question 5You discover that a dbt model is directly referencing a raw object in the database withou…
- Question 6What is the purpose of the dbt deps command in the dbt workflow?
