Constraints
You have tables with the following requirements: ✑ Some records in the table named OrderLines do not exist in the table named Order. ✑ The column named OrderNumber must be a unique value in the Order table. ✑ The OrderNumber column on the OrderLines table allows null values. ✑ The OrderNumber column on the OrderLines table must be validated with the Order table. ✑ Must not allow a new record in the OrderLines table that does not match a record in the Order table.
How should you complete the statements? To answer, select the appropriate transact-SQL segments from the drop-down menus in the answer area. NOTE: Each correct selection is worth one point.
References: https://www.sqlshack.com/commonly-used-sql-server-constraints-foreign-key-check-default
Answer Given: Box 1: PRIMARY KEY - Box 2: NOCHECK - Need NOCHECK as some records in the table named OrderLines do not exist in the table named Order. Box 3: FOREIGN KEY - FOREIGN KEY allows inserting NULL values if there is no NOT NULL constraint defined on this key, but the PRIMARY KEY does not accept NULLs.
Discussion:
othman_ee
1. unique 2. with check because: The OrderNumber column on the OrderLines table must be validated with the Order table. 3. Foreign Key
New_user
1. Set primary key to establish foreign key connection to table. Primary key also ensures uniqueness of records 2. With nocheck constraint doesn't allow to remove existing records in Orderlines 3. Foreign key So, answer looks adorable
tesen_tolga
1. I think it should be unique. 2. I think this is correct. Because there are records on OrderLine table they don't exist on Order table.
Ziviola
1. UNIQUE. The OrderNumber column on the OrderLines table allows null values. Unique constraint allow NULL. 2. NOCHECK. Because there are records on OrderLine table they don't exist on Order table. 3. CHECK(Ordernumber IN (SELECT OrderNumber FROM Orders). Must not allow a new record in the OrderLines table that does not match a record in the Order table.
Bartek
All of Your answers are wrong. If in last spot OrderLines table will apear to itself then we cant fulfill last requirement in exercise. See and test sample code made by me : DROP TABLE IF EXISTS DBO.ORDERS DROP TABLE IF EXISTS DBO.ORDERSLINES CREATE TABLE DBO.ORDERS (ORDERNUMBER INT NOT NULL) CREATE TABLE DBO.ORDERSLINES (ORDERNUMBER INT NULL) INSERT INTO DBO.ORDERS VALUES (1),(2),(3),(4) INSERT INTO DBO.ORDERSLINES VALUES (1),(2),(3),(4),(5),(NULL) ALTER TABLE DBO.ORDERS ADD CONSTRAINT ORDERS_KEY PRIMARY KEY (ORDERNUMBER) -- PRIMARY KEY ALTER TABLE DBO.ORDERSLINES WITH NOCHECK ADD CONSTRAINT ORDERS_CHECK FOREIGN KEY (ORDERNUMBER) REFERENCES DBO.ORDERS(ORDERNUMBER) --ERROR IN THIS PART, SHOULD REFERENCE TO ORDERS TABLE, NOT TO THEMSELF LIKE IN EXERCISE INSERT INTO DBO.ORDERSLINES VALUES (6) -- THIS INSERT WILL NOT WORKS "Must not allow a new record in the OrderLines table that does not match a record in the Order table."
My understanding:
From here, you have two choices. You can (ideally) fix the data, or you can create the constraint and leave the bad data. This can be done by specifying WITH NOCHECK which bypasses the data check. If you check the constraint now to see if it is trusted, it is. If you want to disable (turn off) a CHECK or FOREIGN KEY constraint, you can use NOCHECK in the ALTER TABLE command:
Box 1:
Relevant requirement:
✑ The column named OrderNumber must be a unique value in the Order table.
✑ The OrderNumber column on the OrderLines table allows null values.
✑ The OrderNumber column on the OrderLines table must be validated with the Order table.
So OrderLines table is the fact table and Order table is the dimensional table.
So OrderNumber must be a unique value in the Order table.
So OrderNumber can be lineked by Foreign Key to the OrderLines table.
The questions asked is at the Orders table,
So I think the answer is PRIMARY KEY
Box2:
Relevant requirement:
✑ The OrderNumber column on the OrderLines table allows null values.
✑ The OrderNumber column on the OrderLines table must be validated with the Order table.
you can create the constraint and leave the bad data. This can be done by specifying WITH NOCHECK which bypasses the data check. If you check the constraint now to see if it is trusted, it is. If you want to disable (turn off) a CHECK or FOREIGN KEY constraint, you can use NOCHECK in the ALTER TABLE command.
So I think the answer is WITH NOCHECK
Box3:
✑ The OrderNumber column on the OrderLines table allows null values.
✑ The OrderNumber column on the OrderLines table must be validated with the Order table.
✑ Must not allow a new record in the OrderLines table that does not match a record in the Order table.
CHECK constraints are owned by a schema, just like a table, even though you rarely reference them as such.The CHECK constraint is used to apply an expression predicate to data as it is inserted or updated. When evaluating the predicate of a CHECK constraint, the expression must evaluate to FALSE before the new or changed data is rejected. If a column allows NULL, and the expression does not explicitly reject NULL values, then if you need the constraint to fail on any condition, you must explicitly check for NULL. While the concept of a CHECK constraint is very simple, in practice there is one major thing to remember: in building a database (and possibly answering an exam question), if the requirement says “always” or “must” (as in “the maximum price of a widget must always be less than or equal to 100”) this is a candidate for a constraint. If the requirement is less strict, (as in “the typical maximum price of a widget is 100”), a constraint cannot be used.
FOREIGN KEY allows inserting NULL values if there is no NOT NULL constraint defined on this key, but the PRIMARY KEY does not accept NULLs.
So I think the answer is FOREIGN KEY.