Kuidas säilitada iga elemendi jaoks unikaalset teavet relatsioonilises tabelis?

In a relational database, the uniqueness of a record in a table is determined by the primary key. Each row must have a unique value in the column (or set of columns) designated as the primary key. This ensures that each record can be uniquely identified.

Here are some ways to ensure uniqueness:

1. Auto-increment primary key: The most common approach. A integer field is created that automatically increments for each new record.

   CREATE TABLE users (
       id INTEGER PRIMARY KEY AUTOINCREMENT, // Unique auto-generated ID
       username TEXT,
       email TEXT UNIQUE // Additional uniqueness for email
   );
   

2. UUID (Universally Unique Identifier): If you need to generate unique identifiers on the client side or in a distributed environment, you can use UUID.

   CREATE TABLE products (
       id TEXT PRIMARY KEY, // Unique UUID in text format
       name TEXT,
       price REAL
   );
   

3. Natural key: If uniqueness is guaranteed by one or more existing data fields.

   CREATE TABLE Countries (
       iso_code TEXT PRIMARY KEY, // Country code is unique worldwide
       name TEXT
   );
   

4. Composite key: A combination of multiple columns to ensure record uniqueness.

   CREATE TABLE OrderItems (
       order_id INTEGER,
       product_id INTEGER,
       quantity INTEGER,
       PRIMARY KEY (order_id, product_id) // Uniqueness of the pair order_id + product_id
   );
   

The choice depends on data identification requirements. The main thing is the presence of a primary key.