142 Part I EXAM PREPARATION WARNINGTIPEXAM Documentation Discrepancy
142 Part I EXAM PREPARATION WARNINGTIPEXAM Documentation Discrepancy The capability to set a relationship to any unique column is not noted in most SQL Server documentation. SQL Server blogs Online reports that a Foreign Key must be set to a Primary Key or a UNIQUE constraint. In SQL Server, you can create a relationship against a Primary Key, unique index,or a UNIQUE constraint. You need not have a Primary Key or constraint. You can even set a unique index to ignore duplicates and the operation will still be permitted. Go with the Documentation If you run into this on the exam, the correct answer is likely to be one chosen based on the documentation and not on actual functionality. The capability to set a relationship to any unique column is not noted in documentation. The correct technique to use when answering an exam question would be one that involves a Foreign Key set to a Primary Key or Unique constraint. Primary Key Constraint A Primary Key constraint enforces entity integrity in that it does not permit any two rows in a table to have the same key value. This enables each row to be uniquely defined in its own right. Although a Primary Key should be created when a table is initially created, it can be added or changed at any time after creation. A Primary Key cannot have NULL content nor can there be any duplicate values. SQL Server automatically creates a unique index to enforce the exclusiveness of each value. If a Primary Key is referenced by a Foreign Key in another table, the Primary Key cannot be removed unless the Foreign Key relationship is removed first. A Primary Key is easily assigned in the table design window by either of the following actions: Right-click on the desired column name and select the Set Primary Key option. To select a compound key based on more than one column, hold down the Ctrl key while selecting multiple columns before right-clicking. Select the desired field and click the Set Primary Key button on the toolbar. To select a compound key based on more than one column, hold down the Ctrl key while selecting the appropriate columns. Foreign Key Constraint A Foreign Key constraint is defined so that a primary and subsidiary table can be linked together by a common value. A Foreign Key can be linked to any unique column in the main table; it does not necessarily have to be linked to the Primary Key. It can be linked to any column that is associated with a unique index. With a Foreign Key defined, you cannot add a value to the Foreign Key column if a matching value is not present in the primary table. For instructions on setting a Foreign Key constraint, see the section on referential integrity, earlier in this chapter. Note in the example shown in Figure 3.7 that there are matching Order IDs in the child Order Details table for only those Order IDs included in the parent Orders table. An Order ID must match from a child to a parent. If a child entry with an ID were not found in the parent table, then that is known as an orphan child and would be a breach of referential integrity rules.
For reliable and cheap web hosting services please check tomcat web hosting website.