Defining Composite Unique Keys Oracle creates an index on the columns of a unique key, so a composite unique key can contain a maximum of 16 columns.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.
The UNIQUE constraint ensures no duplicate values in a specific column, which is not a primary key. A table can have only one PRIMARY KEY constraint, however, it can have multiple UNIQUE constraints. Unlike the PRIMARY KEY constraint, you can enter a NULL value for a column that has a UNIQUE constraint.
Put the UNIQUE declaration within the column definition section; working example:
CREATE TABLE a (
i INT,
j INT,
UNIQUE(i, j) ON CONFLICT REPLACE
);
Well, your syntax doesn't match the link you included, which specifies:
CREATE TABLE name (column defs)
CONSTRAINT constraint_name -- This is new
UNIQUE (col_name1, col_name2) ON CONFLICT REPLACE
If you already have a table and can't/don't want to recreate it for whatever reason, use indexes:
CREATE UNIQUE INDEX my_index ON my_table(col_1, col_2);
Be careful how you define the table for you will get different results on insert. Consider the following
CREATE TABLE IF NOT EXISTS t1 (id INTEGER PRIMARY KEY, a TEXT UNIQUE, b TEXT);
INSERT INTO t1 (a, b) VALUES
('Alice', 'Some title'),
('Bob', 'Palindromic guy'),
('Charles', 'chucky cheese'),
('Alice', 'Some other title')
ON CONFLICT(a) DO UPDATE SET b=excluded.b;
CREATE TABLE IF NOT EXISTS t2 (id INTEGER PRIMARY KEY, a TEXT UNIQUE, b TEXT, UNIQUE(a) ON CONFLICT REPLACE);
INSERT INTO t2 (a, b) VALUES
('Alice', 'Some title'),
('Bob', 'Palindromic guy'),
('Charles', 'chucky cheese'),
('Alice', 'Some other title');
$ sqlite3 test.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> CREATE TABLE IF NOT EXISTS t1 (id INTEGER PRIMARY KEY, a TEXT UNIQUE, b TEXT);
sqlite> INSERT INTO t1 (a, b) VALUES
...> ('Alice', 'Some title'),
...> ('Bob', 'Palindromic guy'),
...> ('Charles', 'chucky cheese'),
...> ('Alice', 'Some other title')
...> ON CONFLICT(a) DO UPDATE SET b=excluded.b;
sqlite> CREATE TABLE IF NOT EXISTS t2 (id INTEGER PRIMARY KEY, a TEXT UNIQUE, b TEXT, UNIQUE(a) ON CONFLICT REPLACE);
sqlite> INSERT INTO t2 (a, b) VALUES
...> ('Alice', 'Some title'),
...> ('Bob', 'Palindromic guy'),
...> ('Charles', 'chucky cheese'),
...> ('Alice', 'Some other title');
sqlite> .mode col
sqlite> .headers on
sqlite> select * from t1;
id a b
---------- ---------- ----------------
1 Alice Some other title
2 Bob Palindromic guy
3 Charles chucky cheese
sqlite> select * from t2;
id a b
---------- ---------- ---------------
2 Bob Palindromic guy
3 Charles chucky cheese
4 Alice Some other titl
sqlite>
While the insert/update effect is the same, the id
changes based on the table definition type (see the second table where 'Alice' now has id = 4
; the first table is doing more of what I expect it to do, keep the PRIMARY KEY the same). Be aware of this effect.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With