Hot answers tagged constraint
27
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 ...
14
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 ...
13
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 ...
12
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 ...
11
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 ...
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
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
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
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.
...
7
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
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 ...
6
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 ...
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
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 ...
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
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
In MySQL, RESTRICT and NO ACTION are synonyms:
In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT. [src]
Now, you are asking how this affects a DELETE FROM column1 WHERE first_id='XX' if the table is defined like so:
CREATE TABLE `column2` (
`second_id` int(11) NOT NULL AUTO_INCREMENT,
`first_id` int(11) ...
5
You are using the Adjacency List model, where it is difficult to enforce such a constraint.
You can examine the Nested Set model, where only true hierarchies can be represented (no circular paths). This has other drawbacks though, like slow Inserts/Updates.
5
I have seen 2 main ways of enforcing this:
1, the OLD way:
CREATE TABLE Foo
(FooId BIGINT PRIMARY KEY,
ParentFooId BIGINT,
FooHierarchy VARCHAR(256),
FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )
The FooHierarchy column would contain a value like this:
"|1|27|425"
Where the numbers map to the FooId column. You would then ...
5
I don't think you will see a performance difference between trigger and using cascade. I would prefer ON CASCADE DELETE just because in my opinion it better describes model and reveals creator's intention. Also, if you later use one or another ORM, it will be able to build relationship between entities (surely, I'm talking about 'Data First' approach)if it's ...
5
This is a junction table, and your requirements are handled by the natural key on the Pets table. In this case you have a many-to-many relationship between people and pet species.
Enforcing the rules become more complicated if you allow people to replace pets over time. In that case, you need to add a third column (I usually use a date type in this case) ...
5
Querying a column that can contain nulls is more complex than querying a column that cannot. So also is querying multiple tables more complex than querying one table. I wouldn't let the avoidance of null drive the normalization.
For example, you mentioned that all devices will eventually be disposed and get a disposal date. If there are no other ...
Only top voted, non community-wiki answers of a minimum length are eligible
