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


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


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


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

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

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


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

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


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

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


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


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


8

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


7

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


7

Besides the nice trigger solution by @Rolando, there's another workaround of this problem in MySQL (until CHECK constraints are implemented). So, if you prefer referential integrity constraints and want to avoid triggers (because of the issues in MySQL when you have both in your tables), you can use another small reference table: CREATE TABLE age_allowed ...


7

I personally think it's a matter of taste. The scripts where the constraints are defined through an ALTER statement are a bit more flexible, as you don't need to care about the order of creation (first create all tables, then all PKs, then all FKs). The scripts with embedded constraints are more "self-contained" however. You don't need to look for other ...


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


6

Solution 1: Script all of your foreign keys out and create the tables on the new server without foreign keys. Load the data, then rerun the scripts to create the foreign keys. Solution 2: Backup the database, the restore it to the new server. Solution 3: Run a query similar to this and figure out the dependencies for yourself. Pick the one that sounds ...


6

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


6

It is kind of possible: you can invoke a scalar UDF from you CHECK constraint, and it kind of can detect cycles of any length. Unfortunately, this approach is extremely slow and unreliable: you can have false positives and false negatives. Instead, I would use materialized path. Another way to avoid cycles is to have a CHECK(ID > ParentID), which is ...


6

You can create a function-based index. If the table is named TRANSACTION CREATE UNIQUE INDEX only_one_purchase ON transaction( (case when transaction_type = 'PURCHASE' then order_id else null end) ); This leverages the fact that Oracle doesn't store completely NULL rows in the ...


6

Install the additional module btree_gist as is mentioned in the manual at the location you linked to: You can use the btree_gist extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, after btree_gist is installed, the following constraint will ...


5

user_id, currency_id, and transaction_amount are all defined as NOT NULL columns in dbo.transactions It looks to me that SQL Server has a blanket assumption that an aggregate can produce a null even if the field(s) it operates on are not null. This is obviously true in certain cases: create table foo(bar integer not null); select sum(bar) from foo -- ...


5

Possible approaches using widely implemented technologies: 1) Revoke 'writer' privileges on the table. Create CRUD procedures that ensure the constraint is enforced at transaction boundaries. 2) 6NF: drop the CHAR(1) column. Add a referencing table constrained to ensure its cardinality cannot exceed one: alter table foo ADD UNIQUE (bar); create table ...


5

This kind of problem is another reason why I asked this quiestion: Application Settings in Database If you have an application setting table in your database you could have an entry that would reference the ID of the one record you want to be considered 'special'. Then you would just look-up what the ID is from your settings table, in this way you dont ...


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



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