Tag Info

Hot answers tagged

36

Under the hood a unique constraint is implemented the same way as a unique index - an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query. So for a database ...


20

You can do that in pure SQL. Create a partial unique index in addition to the one you have: CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL; This way you can have (1, 2, 1) and (1, 2, 2) and (1, 2, NULL) for (a, b, c) in your table, but none of these a second time. Additional notes No use for mixed case identifiers ...


10

In other words, you want subset to be unique if type = 'true'. A partial unique index will do that: CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true'; This way you can even make combinations with NULL unique, which is not possible otherwise - as detailed in this related answer: PostgreSQL multi-column unique constraint and NULL ...


8

There is no significant disadvantage using the natural key as the clustered index there are no non-clustered indexes no foreign keys referencing this table (it is a parent row) The downside would be increased page splits as data inserts would be distributed throughout the data, instead of at the end. Where you do have FKs or NC indexes, the using a ...


8

A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints. Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys. Another ...


8

I have set up a test for checking the options. I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the results, I piped the output of the setup commands to /dev/null - on a Windows box one could choose a log file instead). I tried to make different results comparable by using more than one ...


7

UNIQUE (column1, column2) implies UNIQUE INDEX (column1, column2) because the INDEX keyword is optional. So an index is created. However, the MySQL 5.5 docs show that the INDEX (or KEY) keyword is mandatory so UNIQUE (column1, column2) should give an error INDEX (column1, column2) does not mean UNIQUE INDEX (column1, column2): it means an index that does ...


7

Every individual transaction (statement) in SQL Server is atomic, meaning it passes or fails as a unit. If the 999,999th record fails on a constraint violation in a 1,000,000 row insert, all of the other rows get rolled back and the table is exactly as it was before the failed statement was attempted. The same applies for updates and deletes, as well as ...


7

With InnoDB tables, all secondary indexes include the columns of the clustered index (which is the primary key), appended in the end. So your unique index has actually 4 columns, the 3 you have defined plus the 1 primary key column. When running a query that needs a full table scan, both indexes have all the data needed, so the optimizer is free to choose ...


6

You could have a third table which stores both. Something like this: unique_ids ---------- identifier (UNIQUE) used_where (name/id of table that the identifier is used in) users ----- username (FK to unique_ids.identifier) clients ------- clientname (FK to unique_ids.identifier) This way, both tables share one common table that contains the ...


6

Your rule does not have a WHERE clause and any update is trying to modify the whole users table. You can/should add WHERE id = old.id: CREATE OR REPLACE RULE students_data_update AS ON UPDATE TO students_data DO INSTEAD UPDATE users SET f_name = new.f_name, l_name = new.l_name, --- ... WHERE id = old.id ; --- added Note: I ...


5

In addition to the differences posted in other answers (including some that I think were unfairly downvoted) there are some key differences between the two. Note: The error messages are from SQL Server 2012. Errors Violation of a unique constraint returns error 2627. Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot ...


5

I wouldn't even consider forcing data of different types into one field. Another option: synthetic post_id and child tables for each 'type' of 'native_post_id' There are various ways to go about enforcing the subset relationship between these child tables and the parent if necessary


5

A Null can mean that value is not known for that row at the moment but will be added, when known, in the future (example FinishDate for a running Project) or that no value can be applied for that row (example EscapeVelocity for a black hole Star). In my opinion, it's usually better to normalize the tables by eliminating all Nulls. In your case, you want to ...


5

Why not just collate that column as case-sensitive? I don't think you need to resort to binary. This seems to work fine, just note that it may affect sorting, comparisons, unions etc: CREATE TABLE [dbo].[Unit] ( [id] SMALLINT NOT NULL IDENTITY(1,1), [name] VARCHAR(100) COLLATE Latin1_General_CS_AS NOT NULL , ...


5

I have to presume that the application is written in such a way that it tries the insert, and if it fails, it just tries again. Otherwise the insert will fail (PK violation) and your users will have complained and filed bugs. If the purpose of this is to just assign unique numbers in a random order, then it may be the case that the application is working ...


5

There's nothing special that would prevent you from using a standard unique constraint here, unless there's something you aren't telling us. When a unique constraint is created on a set of nullable columns, NULL is treated (in terms of uniqueness) like any other of the distinct values in the set. (I believe this behaviour varies across RDBMSes.) So I think ...


5

There is no need to drop and recreate the index. Just use ALTER TABLE dbo.production_data ALTER COLUMN serial NVARCHAR(32) NOT NULL; This is a metadata only change. Altering a column from NVARCHAR(16) to NVARCHAR(32) does not affect the storage at all. Going the other way round (from NVARCHAR(32) to NVARCHAR(16)) would give you an error about ...


4

Change the table's definition by adding a UNIQUE KEY constraint on the combination of the two columns: CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, network_contact_id int, CONSTRAINT network_id_contact_id_UNIQUE UNIQUE KEY (network_id, network_contact_id) ); You ...


4

I would definitely go with a hash of the url and make the hash a unique index. A hash has a fixed length, so you can use CHAR to specify the length of the column, which grants a slight performance boost over VARCHAR or TEXT. But might I suggest using INSERT IGNORE instead of making two calls to the database? Something like: INSERT IGNORE INTO urlTable ...


4

For starters, I would not touch the buffer sizes just yet. The sizes youhave in the question are monstrously too big. Here is another observation: You have BLOB data. Ouch, your temp table is going to eat space rather quickly. You could do somehting like this: Create a 32GB RAM Disk called /var/tmpfs by adding this line to /etc/fstab none ...


4

In a standard SQL dbms, you'd enforce that kind of requirement by ordering the id numbers, and using a CHECK constraint. Application code, a stored procedure, or a user-defined function is responsible for putting the id numbers in the right order. create table friends ( user_a integer not null, -- references users, not shown user_b integer not null, ...


4

There are two schools of thought about this and some of it is database dependent. In general, on PostgreSQL, I define natural primary keys wherever I can (and make these composite keys where appropriate) and I (usually) add a secondary, surrogate id key which is an integer and is used as a surrogate for the primary key in joins. I see this as a good way to ...


4

This is supplemental to Erwin's answer above, but PostgreSQL supports a bunch of types of indexes. These are not generally mutually exclusive. You can think of these as being: Index method (btree, GiST, GIN, etc). Choose one, if necessary (btree being the default) Partial or full. If partial use a where clause Direct or functional. You can index the ...


4

You can't create a foreign key that targets a relation without a unique constraint in PostgreSQL. I'm somewhat astonished to hear that you can in MySQL. It's really against the principle of a "foreign key" - if you can have multiple values, then the target is not in fact a key at all, it's just another data column. That said, PostgreSQL isn't just ...


3

I would look at your model first. Placing a unique key constraint on a non-unique column gets you into this kind of problem. What happens when you get a legitimate value like test title-2 but you've already used that value to resolve a collision on test title. If I had to resolve your problem, I would build a query for each unique key of the form: SELECT ...


3

This is fairly simple to accomplish by making some changes to your primary keys. http://sqlfiddle.com/#!2/8dcdd/1 I changed the primary key of the UserEventRoles table to be a composite key of user_id and event_id. This forces each User to only ever have at most one Role per Event. I also modified the EventRoles table to use composite primary key. The ...


3

For now, I see two possible problems in your model: Member of a team can exist without a reference to Student. 1.1. If you delete a Member with ON DELETE CASCADE foreign key option in Student it will remove your student. 1.2. If you delete a Student, there might be a Member, that not referenced to any Student. If you would like to place Member properties ...


3

I was thinking because this was a calculation it'd have to be a trigger but in doing some googling there are better options. Overlapping intervals look like they can be blocked by a particular pattern of constraints (http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx) This slideshare walks ...


3

The query to accomplish this would be of the form: SELECT t.UniqueKey FROM mytable t WHERE t.UniqueKey = ? AND t.timeStamp >= NOW() - INTERVAL 5 MINUTE AND t.timeStamp <= NOW() LIMIT 1 (This query assumes that the timeStamp column is defined as datatype TIMESTAMP.) The query will either return one or zero rows, which will indicate either ...



Only top voted, non community-wiki answers of a minimum length are eligible