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 questions

Domain Modeling

Answer options
A: CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT FOREIGN KEY, REFERENCES customers(id));
B: CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT FOREIGN KEY REFERENCES customers(id)); Correct answer
C: CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, FOREIGN KEY customer_id REFERENCES customers(id));
D: CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT REFERENCES customers(id), FOREIGN KEY);
Correct Answer:
B. CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT FOREIGN KEY REFERENCES customers(id));
Explanation

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.

Why Incorrect

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.

References

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 ()).

Scroll to Top