modul:m290:learningunits:lu05:theorie:03

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290:learningunits:lu05:theorie:03 [2024/09/27 12:05] – vdemirmodul:m290:learningunits:lu05:theorie:03 [2024/10/17 12:42] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU06c - SQL-DDL: Constraint Management ======+====== LU07c - SQL-DDL: Constraint Management ======
  
 **Source**:  **Source**: 
-  * [[https://www.w3schools.com/sql/sql_constraints.asp | W3Schools | Constraints | Overview]] +  - [[https://www.w3schools.com/sql/sql_constraints.asp | W3Schools | Constraints | Overview]] 
-  * [[https://www.w3schools.com/sql/sql_primarykey.asp| W3Schools | Constraints | PRIMARY KEY]] +  - [[https://www.w3schools.com/sql/sql_primarykey.asp| W3Schools | Constraints | PRIMARY KEY]] 
-  * [[https://www.w3schools.com/sql/sql_foreignkey.asp| W3Schools | Constraints | FOREIGN KEY]]  +  - [[https://www.w3schools.com/sql/sql_autoincrement.asp W3Schools |  W3Schools | Constraints | AUTO_INCREMENT]] 
-  * [[https://www.w3schools.com/sql/sql_notnull.asp | W3Schools | Constraints | NOT NULL]] +  - [[https://www.w3schools.com/sql/sql_foreignkey.asp| W3Schools | Constraints | FOREIGN KEY]]  
-  * [[https://www.w3schools.com/sql/sql_unique.asp | W3Schools | Constraints | UNIQUE]]+  - [[https://www.w3schools.com/sql/sql_notnull.asp | W3Schools | Constraints | NOT NULL]] 
 +  - [[https://www.w3schools.com/sql/sql_unique.asp | W3Schools | Constraints | UNIQUE]]
  
 ===== Learning Objectives ===== ===== Learning Objectives =====
Zeile 21: Zeile 22:
 MySQL constraints ensure data integrity, enforcing rules at the database level. CONSTRAINTS restrict the type of data that can be inserted into tables, preventing invalid entries and ensuring relationships between tables remain accurate. The most common constraints in MySQL are  MySQL constraints ensure data integrity, enforcing rules at the database level. CONSTRAINTS restrict the type of data that can be inserted into tables, preventing invalid entries and ensuring relationships between tables remain accurate. The most common constraints in MySQL are 
  
-  * Primary Key  +  - PRIMARY KEY  
-  * Foreign Key  +  - AUTO_INCREMENT 
-  * NOT NULL +  - NOT NULL 
-  * AUTO_INCREMENT +  - UNIQUE 
-  * UNIQUE+  - FOREIGN KEY  
  
 Let’s explore these CONSTRAINTS with their syntax and practical examples. Let’s explore these CONSTRAINTS with their syntax and practical examples.
  
-==== PRIMARY KEY ====+==== 1. PRIMARY KEY ====
 The Primary Key constraint uniquely identifies each record in a table. A primary key column (or a set of columns) must contain unique, non-null values. Each table can have only one primary key.  The Primary Key constraint uniquely identifies each record in a table. A primary key column (or a set of columns) must contain unique, non-null values. Each table can have only one primary key. 
  
Zeile 48: Zeile 49:
   );   );
  
-==== FOREIGN KEY ==== +==== 2. AUTO INCREMENT ==== 
-The Foreign Key constraint ensures referential integrity by linking a column in one table to the primary key of another. It establishes a relationship between two tables and enforces the rule that data in the foreign key column must match an existing primary key in the referenced table.+The AUTO_INCREMENT attribute automatically generates a unique number for new rows. It is typically used with the primary key to create unique identifiers without manual input.
  
 ** General Syntax ** ** General Syntax **
  
   CREATE TABLE table_name (   CREATE TABLE table_name (
-    column_name1 datatype, +    column_name datatype AUTO_INCREMENT, 
-    column_name2 datatype, +    PRIMARY KEY (column_name)
-    FOREIGN KEY (column_name1) REFERENCES another_table(primary_key_column)+
   );   );
-  +
 ** Example ** ** Example **
-  CREATE TABLE orders ( +  CREATE TABLE products ( 
-    order_id INT PRIMARY KEY, +    product_id INT AUTO_INCREMENT PRIMARY KEY, 
-    customer_id INT, +    product_name VARCHAR(50)
-    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)+
   );   );
  
-In this example, customer_id in the orders table references the customer_id in the customers table. This relationship ensures that any customer_id in the orders table corresponds to an existing customer in the customers table.+Here, product_id automatically increments each time a new row is inserted, ensuring a unique ID for every product.
      
-==== NOT NULL ====+==== 3. NOT NULL ====
 The NOT NULL constraint ensures that a column cannot contain a NULL value. It is used when the column must always have a value.    The NOT NULL constraint ensures that a column cannot contain a NULL value. It is used when the column must always have a value.   
  
Zeile 86: Zeile 85:
 In this example, the employee_name column is constrained to never contain a NULL value, ensuring every employee has a name. In this example, the employee_name column is constrained to never contain a NULL value, ensuring every employee has a name.
  
-==== AUTO INCREMENT ==== +==== 4. UNIQUE ==== 
-The AUTO_INCREMENT attribute automatically generates a unique number for new rows. It is typically used with the primary key to create unique identifiers without manual input.+The UNIQUE constraint ensures that all values in a column (or a set of columns) are unique across the table. Unlike the primary key, a table can have multiple unique constraints.
  
 ** General Syntax ** ** General Syntax **
- 
   CREATE TABLE table_name (   CREATE TABLE table_name (
-    column_name datatype AUTO_INCREMENT, +    column_name datatype UNIQUE
-    PRIMARY KEY (column_name)+
   );   );
 +  
 +** Example **
  
-** Example ** +  CREATE TABLE users ( 
-  CREATE TABLE products ( +    user_id INT PRIMARY KEY, 
-    product_id INT AUTO_INCREMENT PRIMARY KEY, +    email VARCHAR(100) UNIQUE
-    product_name VARCHAR(50)+
   );   );
  
-Here, product_id automatically increments each time a new row is inserted, ensuring a unique ID for every product.+In this case, the email column must contain unique values. No two users can have the same email address.
  
-==== UNIQUE ==== +==== 5. FOREIGN KEY ==== 
-The UNIQUE constraint ensures that all values in a column (or a set of columns) are unique across the table. Unlike the primary key, a table can have multiple unique constraints.+The Foreign Key constraint ensures referential integrity by linking a column in one table to the primary key of another. It establishes a relationship between two tables and enforces the rule that data in the foreign key column must match an existing primary key in the referenced table.
  
 ** General Syntax ** ** General Syntax **
 +
   CREATE TABLE table_name (   CREATE TABLE table_name (
-    column_name datatype UNIQUE+    column_name1 datatype, 
 +    column_name2 datatype, 
 +    FOREIGN KEY (column_name1) REFERENCES another_table(primary_key_column)
   );   );
      
 ** Example ** ** Example **
- +  CREATE TABLE orders ( 
-  CREATE TABLE users ( +    order_id INT PRIMARY KEY, 
-    user_id INT PRIMARY KEY, +    customer_id INT, 
-    email VARCHAR(100) UNIQUE+    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );   );
  
-In this case, the email column must contain unique values. No two users can have the same email address.+In this example, customer_id in the orders table references the customer_id in the customers table. This relationship ensures that any customer_id in the orders table corresponds to an existing customer in the customers table.
  
 ===== Practical Example with Multiple Constraints ===== ===== Practical Example with Multiple Constraints =====
Zeile 132: Zeile 133:
   );   );
 ===== Summary ===== ===== Summary =====
 +In summary, MySQL constraints like Primary Key, Foreign Key, NOT NULL, AUTO_INCREMENT, and UNIQUE are essential for maintaining data accuracy, integrity, and consistency across a database. They enforce rules at the database level, ensuring reliable data relationships and preventing errors.
 +
 +  * **PRIMARY KEY** is applied to student_id to uniquely identify each data record in the table. 
   * **AUTO_INCREMENT** is applied to student_id to automatically generate a unique ID for each student.   * **AUTO_INCREMENT** is applied to student_id to automatically generate a unique ID for each student.
   * **NOT NULL** is applied to student_name, requiring every student to have a name.   * **NOT NULL** is applied to student_name, requiring every student to have a name.
Zeile 141: Zeile 145:
   * **Preventing Errors:** Constraints like NOT NULL prevent the insertion of incomplete or invalid data.   * **Preventing Errors:** Constraints like NOT NULL prevent the insertion of incomplete or invalid data.
   * **Automation: AUTO_INCREMENT** simplifies the process of assigning unique identifiers without user input.   * **Automation: AUTO_INCREMENT** simplifies the process of assigning unique identifiers without user input.
- 
-In summary, MySQL constraints like Primary Key, Foreign Key, NOT NULL, AUTO_INCREMENT, and UNIQUE are essential for maintaining data accuracy, integrity, and consistency across a database. They enforce rules at the database level, ensuring reliable data relationships and preventing errors. 
- 
- 
- 
  
 ===== Vocabulary ===== ===== Vocabulary =====
  • modul/m290/learningunits/lu05/theorie/03.1727431520.txt.gz
  • Zuletzt geändert: 2024/09/27 12:05
  • von vdemir