39

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 the referenced table. ...


20

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


18

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the chicken-and-egg problem of inserting a user and his active email, ...


16

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


15

Variant 1 Since all you need is a single column with standard = true, set standard to NULL in all other rows. Then a plain UNIQUE constraint works, since NULL values do not violate it: CREATE TABLE taxrate ( taxrate int PRIMARY KEY , standard bool DEFAULT true , CONSTRAINT standard_true_or_null CHECK (standard) -- yes, that's the whole constraint , ...


13

Lets start with your first link. It says clearly: Working on large databases with referential integrity just does not perform well. And that is right. Just you likely have no clue that "large database" is terabyte size with billions of rows in a table. A simple select may cascade into hundreds of millions of related elements to be deleted, and then you ...


12

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


12

That is the whole point of foreign key constraints: they stop you deleting data that is referred to elsewhere in order to maintain referential integrity. There are two options: Delete the rows from INVENTORY_ITEMS first, then the rows from STOCK_ARTICLES. Use ON DELETE CASCADE for the in the key definition. 1: Deleting In Correct Order The most ...


11

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};


11

As discussed in the comments violation of a foreign key constraint will produce an error on most legitimate server products. However, this is only when the foreign key has been defined physically. A logical foreign key can exist without the enforcer that on most engines is called the constraint. I've seen a lot of schemas that show relations on paper ...


11

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


10

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


9

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


9

If you have defined the Foreign Key constraints as ON UPDATE CASCADE then the Primary Key value that was changed should cascade down to all the Foreign Keys with that constraint. If you do not have the ON UPDATE CASCADE constraint, then you will need create scripts to complete the update. EDIT: Since you do not have the ON UPDATE CASCADE constraint, but ...


9

You can use a Filtered index create table test ( id int primary key, foo bool ); CREATE UNIQUE INDEX only_one_row_with_column_true_uix ON test (foo) WHERE (foo); --> where foo is true insert into test values (1, false); insert into test values (2, true); insert into test values (3, false); insert into test values (4, false); insert into ...


8

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


8

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.


8

FULL vs SIMPLE vs PARTIAL While the chosen answer is correct, if this is new to you, you may want to see it with code -- I think it's easier to grok that way. -- one row with (1,1) CREATE TABLE foo ( a int, b int, PRIMARY KEY (a,b) ); INSERT INTO foo (a,b) VALUES (1,1); -- -- two child tables to reference it -- CREATE TABLE t_full ( a int, b int, ...


8

The only issue is that there isn't really a convenient way to enforce these relationships. You can use a boatload of triggers, or store the master table in each instance database and simply expose all of them as metadata to the master, using a view perhaps. The biggest downside, I think, has to do with keeping the data accurate and synchronized, in at least ...


7

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


7

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


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

If you can add a column to the table, the following scheme would almost1 work: CREATE TABLE emails ( UserID integer NOT NULL, EmailAddress varchar(254) NOT NULL, IsActive boolean NOT NULL, -- New column ActiveAddress varchar(254) NOT NULL, -- Obvious PK CONSTRAINT PK_emails_UserID_EmailAddress PRIMARY KEY (UserID, ...


6

As you simply cannot create a foreign key against an array column, you should store the experiments in the proper relational way. This is what you mention as a junction table. It is a table like the following: CREATE TABLE experiments ( method_id integer REFERENCES methods (method_id), method_class_id integer REFERENCES method_class (...


6

I thought about using ROW_NUMBER() in the trigger as a sort of pseudo-column... Stop thinking this. There is no guarantee of row order in the inserted or deleted pseudo-tables. You may observe it "working", but you will be relying on undocumented behaviour. The standard workaround for the lack of an exposed unique row identifier to correlate updated rows (...


5

Unfortunately there isn't a cookie-cutter way to do this. You have to start with some base records, write queries that join against them and then repeat. As you go deeper the queries get more complex as they have to go right back to the root. If you have the foreign keys then you can automate the generation of the queries, as the joins will just be ...


5

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table: One-To-Many create table Producer ( id int identity(1, 1) not null primary key clustered, Name varchar(100) not null ) go ...


5

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


5

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


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.Friend1ID=b.Friend2ID and a.Friend2ID=b.Friend1ID) where b.friend1ID IS NULL ;


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