SQL multiple table same unique id

Hello, How can I create same unique id in multiple tables.

CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        username TEXT    NOT NULL,
        email TEXT)

CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        username TEXT    NOT NULL,
        email TEXT)

CREATE TABLE IF NOT EXISTS order (
        id FROM users(id),
        address TEXT)

CREATE TABLE IF NOT EXISTS order (
        id FROM users(id),
        address TEXT)

4 Replies

Choo♚𝕂𝕚𝕟𝕘•3d ago

If neither table used autoincrement on id, you could generate something like a UUID and give the same code in both tables. If one table uses autoincrement, you would need to insert into that table first, get the id, and use it when inserting into the other table. There might exist some kind of non-standard optimization for this, but I am not aware of a more efficient approach in standard SQL.

lanszelotOP•3d ago

Thank you for your response. constraint, or foreign key or something like that can create table which automatically use another table column , but I don't know how.

Choo♚𝕂𝕚𝕟𝕘•2d ago

A foreign key constraint does not use any value from another table. It is a rule that requires the value to be from another table, but it doesn't give you a value. It's like having a rule that a number must be positive. That rule doesn't give you any specific positive number but giving a negative number will trigger an error.

13eck•2d ago

A FK constraint, as mentioned, doesn't fill the entry with that specific item, it says "this entry has to be a value found in fk table/field". So id FROM users(id) tells SQL that when you add a row to the order table (should be orders, though, since it'll contain more than one order) it needs to verify that the value you gave it for the id field matches a value in the users.id column.

Gaming

Programming

SQL multiple table same unique id

Did you find this page helpful?