Tag Info

Hot answers tagged

42

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 ...


28

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 ...


23

SQL Server 2008 - Filtered unique index CREATE UNIQUE INDEX IX_Foo_chk ON dbo.Foo(chk) WHERE chk = 'Y'


16

CHECK constraints are not implemented in MySQL. From CREATE TABLE The CHECK clause is parsed but ignored by all storage engines. See Section 12.1.17, “CREATE TABLE Syntax”. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with ...


15

Actually, I had to do something like this once. It involved creating a computed column that takes the value of the Unique column when is not NULL and the value of the primary key (with some other logic to make it impossible to clash with the values on the unique column), and making the unique index on that column. You can see an example of this and the ...


14

SQL Server 2000, 2005: You can take advantage of the fact that only one null is allowed in a unique index: create table t( id int identity, chk1 char(1) not null default 'N' check(chk1 in('Y', 'N')), chk2 as case chk1 when 'Y' then null else id end ); create unique index u_chk on t(chk2); for 2000, you may need SET ...


14

Edit: Update following correction to original question. The following should do the trick: CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK ( (CASE WHEN col1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END + CASE ...


13

Oracle: Since Oracle doesn't index entries where all indexed columns are null, you can use a function-based unique index: create table foo(bar integer, chk char(1) not null check (chk in('Y', 'N'))); create unique index idx on foo(case when chk='Y' then 'Y' end); This index will only ever index a single row at most. Knowing this index fact, you can ...


13

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 ...


12

I think that you would do much better to put your unique index check on a computed column that is generated using HASHBYTES('MD5', ...) on the combination of your 18 columns.


12

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 ...


11

innodb foreign key. It's just short hand naming convention. You could call it asdfqwerty and the name would still work.


10

I think this is a case of structuring your database tables correctly. To make it more concrete, if you have a person with multiple addresses and you want one to be the default, I think you should store the addressID of the default address in the person table, not have a default column in the address table: Person ------- PersonID Name etc. DefaultAddressID ...


10

This should do it I think. CREATE TABLE Foo ( FooId INT PRIMARY KEY, Active BIT NOT NULL, UNIQUE(FooId, Active) ) CREATE TABLE FooActive ( FooId INT PRIMARY KEY, Active AS CAST(1 AS BIT) PERSISTED, FOREIGN KEY (FooId, Active) REFERENCES Foo(FooId, Active) ) CREATE TABLE FooInActive ( FooId INT PRIMARY KEY, Active AS CAST(0 AS BIT) PERSISTED, FOREIGN ...


10

Rolling your own referential integrity checks has the following disadvantages: Speed - Your own checks will never be as fast as database internal checks. Completeness- There is always the possibility when you roll your own that you will miss something. The speed issue is acceptable if the high performance of the database engine can make up for the loss, ...


10

I don't know that there's an official or technical name for the relationship you're describing (I also don't know if you'll find many pet lovers who would like to be restricted to one dog, one cat, etc.), but here is how I would probably approach this: CREATE TABLE dbo.People ( PersonID INT PRIMARY KEY --, ... name, etc. ); CREATE TABLE dbo.PetTypes ( ...


10

Rather than abort the update, just override it? ... FOR EACH ROW BEGIN SET NEW.Schueler_ID = OLD.Schueler_ID; SET NEW.Klasse_ID = OLD.Klasse_ID; END


9

PostgreSQL: create table foo(bar serial, chk char(1) unique check(chk='Y')); insert into foo default values; insert into foo default values; insert into foo(chk) values('Y'); select * from foo; bar | chk -----+----- 1 | 2 | 3 | Y insert into foo(chk) values('Y'); ERROR: duplicate key value violates unique constraint "foo_chk_key" --edit or ...


9

SQL Server: How to do it: The best way is a filtered index. Uses DRI SQL Server 2008+ Computed column with uniqueness. Uses DRI See Jack Douglas' answer. SQL Server 2005 and before An indexed/materialised view which is like a filtered index. Uses DRI All versions. Trigger. Uses Code, not DRI. All versions How not to do it: Check constraint with a ...


9

MySQL: create table foo(bar serial, chk boolean unique); insert into foo(chk) values(null); insert into foo(chk) values(null); insert into foo(chk) values(false); insert into foo(chk) values(true); select * from foo; +-----+------+ | bar | chk | +-----+------+ | 1 | NULL | | 2 | NULL | | 3 | 0 | | 4 | 1 | +-----+------+ insert into foo(chk) ...


9

You probably don't want to use rules and instead use constraints, in this case a check constraint. The reason that you don't want to use rules is that rules have been deprecated means that they will be removed from SQL Server at some point in the future so it would be better to use the check constraint instead. Something like this will do the trick. ...


9

Create a unique index or unique constraint on UserName then you can reference it in a FK constraint fine. Your statement that Sql Server doesn't allow me to create a relationship on a non primary key column is incorrect. SQL Server only cares that the column(s) participating in the FK relationship have a unique index defined.


9

What is the cost of data quality issues down the line from this system? Do those costs outweigh the benefits of using this system over some other system that actually enforces referential integrity? Unfortunately, there are plenty of applications out there (particularly those of the "database agnostic" variety) that implement their own constraints rather ...


9

Add a persisted computed column that combines the 18 keys, then create an unique index on the computed column: alter table t add all_keys as c1+c2+c3+...+c18 persisted; create unique index i18 on t (all_keys); See Creating Indexes on Computed Columns. Another approach is to create an indexed view: create view v with schemabinding as select ...


9

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 ...


9

Proper solution The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this: CREATE TABLE name ( name_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, verified BOOLEAN NOT NULL DEFAULT FALSE, UNIQUE (name, email) ); ...


8

To allow strings containing only spaces but disallow empty strings you can use CREATE TABLE varchar_without_empty_cols ( id INT, val VARCHAR(10) NOT NULL CHECK (DATALENGTH(val) > 0) )


8

What you need are two triggers to catch the invalid age condition BEFORE INSERT BEFORE UPDATE The following is based on a jerry-rigged error trapping method for MySQL Triggers from Chapter 11, Pages 254-256 of the book MySQL Stored Procedure Programming under the subheading 'Validating Data with Triggers': drop table mytable; create table mytable ( ...


8

Have you tried the --disable-triggers option to pg_restore? Per the documentation: Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload. Please note that this only is valid for a --data-only restore and requires the --superuser=username option to be passed, as well.


7

You can make a view (Where Username is not null) then put a unique index against the view. You'll never need to use the view, it'll just exist for this. You can also use this technique to enforce uniqueness across several tables, where it would normally be impossible.



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