Tag Info

Hot answers tagged

24

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


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


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

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

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


7

No, there is no such hint. What you can do is alter table {child_table} disable constraint {fk_constraint_name}; truncate table {parent_table}; alter table {child_table} enable constraint {fk_constraint_name};


7

In Oracle, one way to enforce this sort of constraint in a declarative fashion would be to create a materialized view that is set to refresh fast on commit whose query identifies all the invalid rows (i.e. BookAspectRating rows that have no match in BookAspect_view). You can then create a trivial constraint on that materialized view that would be violated ...


7

I think you'll find that in a lot of cases, complex business rules cannot be enforced via the model alone. This is one of those cases where, at least in SQL Server, I think a trigger (preferably an instead of trigger) better serves your purpose.


7

I think you can, using a "diamond" relationship diagram: CREATE TABLE Artist ( artistID INT NOT NULL , name VARCHAR(100) NOT NULL , PRIMARY KEY (artistID) ) ; CREATE TABLE Album ( artistID INT NOT NULL , albumID INT NOT NULL , title VARCHAR(100) NOT NULL , PRIMARY KEY (artistID, albumID) , FOREIGN KEY (artistID) REFERENCES Artist (artistID) ) ; ...


7

The behaviour is not explicitly mentioned in any of the official SQL Server documentation I am familiar with, but the 1992 Draft SQL Standard (section 11.8.2.b) does say: If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that ...


6

This business rule can be enforced in the model using only constraints. The following table should solve your problem. Use it instead of your view: CREATE TABLE BookAspectCommonTagLink ( BookID INT NOT NULL , AspectID INT NOT NULL , TagID INT NOT NULL --TagID is deliberately left out of PK , PRIMARY KEY (BookID, AspectID) , FOREIGN ...


6

One radical solution might be to remove pin_inst completely: part ←────────── pin ↑ ↑ part_inst ←───── pin_inst There is nothing in your question to suggest you actually need the redundant table. For pins associated to a part_inst, look at the pins of the associated part. That would simplify the code to: create table part ( -- ...


6

Support for array foreign keys was worked on with the goal of getting it into PostgreSQL 9.3, but it didn't make the cut for the release due to performance and reliability problems. It doesn't seem to be being worked on for 9.4. At this time, you need to stick to the usual relational approach of using a "join table" to model an m:n relationship. CREATE ...


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

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

The restrictions on truncating tables include: You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential. This is presumably because truncate is DDL and doesn't do any checks ...


5

To find the rows, use a left outer join: select a.Friend1ID, a.Friend2ID, b.Friend1ID, b.Friend2ID from Friends a left join Friends b on (a.FriendID1=b.Friend2ID and a.Friend2ID=b.Friend1ID) where b.friend1ID IS NULL ;


5

To counter the points directly: Drupal doesn't use them and gets along fine without them, so why should we? Drupal supports many database layers, perhaps at least one of those does not support FKs and they chose to stick with the lowest common feature set? A great many people do use them, the one data point where people aren't using them is relatively ...


4

For your second option, you could put a filtered unique index on: CREATE UNIQUE INDEX ix_DefaultLocation ON dbo.Locations(EventID, IsDefault) WHERE IsDefault = 1 This will allow unlimited non-default locations, but only one default location per event id.


4

No, there is no value in adding an additional "primary key" to this table. Your joins are only ever going to refer to ProducerID and ProductID, so it is just dead weight. IMHO. Though I agree with @Shark that the join table doesn't even seem to be needed here, unless you are going out of your way to not change the schema of the existing tables in any way. ...


4

For those who use MySQL, here is an appropriate Stored Procedure: DELIMITER $$ DROP PROCEDURE IF EXISTS SetDefaultForZip; CREATE PROCEDURE SetDefaultForZip (NEWID INT) BEGIN DECLARE FOUND_TRUE,OLDID INT; SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE; IF FOUND_TRUE = 1 THEN SELECT ID INTO OLDID FROM PostalCode ...


4

Your table will work fine for this purpose, but you probably want to add an index. If the primary reason for using this table is to take an outside_ticket_id and get the corresponding ticket_id's I would add the following clustered index: CREATE CLUSTERED INDEX [CL_Lookup_OD_ID] on [lookup](outside_data_id) GO If the primary lookup will be the other way ...


4

A structure like yours should probably be solved with: - a multi-column primary key constraint on the m-table (lookup) and - a foreign key constraint referencing the primary key of the 1-table. An optimal index for looking up values in one direction is provided automatically by the primary key of the lookup table. CREATE TABLE ticket ( ticket_id integer ...


4

The simplest approach is to store each relationship exactly once, and enforce that with a check constraint Friend1 CREATE VIEW AllFriendships AS SELECT Friend1, Friend2 FROM Friendships UNION ALL SELECT Friend1 AS Friend2, Friend2 AS Friend1 FROM Friendships If, however, you really need the table with both Friend1,Friend2 and Friend2,Friend1, you could ...


4

Like @ypercube commented, check the CREATE TABLE page of the manual: There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in ...


4

Implementing this stuff at an app level is a nightmare. You and your team will have to test, double check and retest code which does EXACTLY the same thing that's been done by MySQL (for InnoDB) for MILLIONS of users over a period of YEARS. Follow the discussion (one of the best threads I've seen on stackoverflow) here. With all due respect to you and your ...


3

SQL Replication isn't going to handle that for you. You'll have to setup a very custom replication type system, possibly using SQL Service Broker to handle the merging of data into a single system for reporting.



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