Exam 70-762 topic 1 question 145 discussion

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.

1. unique 2. with check because: The OrderNumber column on the OrderLines table must be validated with the Order table. 3. Foreign Key

NOCHECK provides the requirement for NULL values in OrderNumber column of OrderLines table . And or course Foreign Key in box 3

Unique constraint would only allow one NULL value in OrderNumber column in Orders Table. Requerments talk about NULL values in OrderLines table.In my opinion It could be a Primary Key.

ALTER TABLE DBO.ORDERSLINES WITH NOCHECK ADD CONSTRAINT ORDERS_CHECK check(ordernumber in (Select ordernumber from ORDERS)) This cause an error Sub-queries are not allowed in this context. Only scalar expressions are allowed. You can't have sub queries inside check constraints. What you can do is use a UDF that returns a scalar value inside the check constraint.

Unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint. see https://docs.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints?view=sql-server-ver15

both primary key and unique will work here . having Primary key on order table , will still allow to enter null order number in orderline table .

foreign key ... reference... have a syntax error, is impossibile to reference itself

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."

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.