No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:
CREATE TABLE dbo.foo(bar INT PRIMARY KEY);
CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));
(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6
and FK__blat__bar__1B1EE1BE
.)
You would say:
CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));
CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar)
REFERENCES dbo.foo(bar));
Enforcing option (c), using a type
attribute:
CREATE TABLE Computer
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType IN ('D', 'L'))
) ;
CREATE TABLE Laptop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, battery WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'L')
, FOREIGN KEY (computerType, computerID)
REFERENCES Computer (computerType, computerID)
) ;
CREATE TABLE Desktop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, monitor WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'D')
, FOREIGN KEY (computerType, computerID)
REFERENCES Computer (computerType, computerID)
) ;
and using deferred foreign key constraints on nullable columns:
CREATE TABLE Computer
( computerID INT NOT NULL
, laptopID INT NULL -- these 2 columns
, desktopID INT NULL -- are NULLable
, PRIMARY KEY (computerID)
, UNIQUE (laptopID)
, UNIQUE (desktopID)
, CHECK ( laptopID IS NOT NULL AND desktopID IS NULL -- but only one of them
OR desktopID IS NOT NULL AND laptopID IS NULL -- is NULL
)
, CHECK (laptopID = computerID) -- and the non-NULL one is
, CHECK (desktopID = computerID) -- equal to computerID
) ;
CREATE TABLE Laptop
( laptopID INT NOT NULL
, battery VARCHAR(20) NOT NULL
, PRIMARY KEY (laptopID)
, FOREIGN KEY (laptopID)
REFERENCES Computer (computerID)
) ;
CREATE TABLE Desktop
( desktopID INT NOT NULL
, monitor VARCHAR(20) NOT NULL
, PRIMARY KEY (desktopID)
, FOREIGN KEY (desktopID)
REFERENCES Computer (computerID)
) ;
ALTER TABLE Computer
ADD CONSTRAINT Laptop_Computer_FK
FOREIGN KEY (laptopID)
REFERENCES Laptop (laptopID)
DEFERRABLE INITIALLY DEFERRED
, ADD CONSTRAINT Desktop_Computer_FK
FOREIGN KEY (desktopID)
REFERENCES Desktop (desktopID)
DEFERRABLE INITIALLY DEFERRED ;
Tested in Postgres, at SQL-Fiddle
Best Answer
It's not easy to do in SQL but it is not impossible. If you want this enforced through DDL alone, the DBMS has to have implemented
DEFERRABLE
constraints. This could be done (and can be checked to work in Postgres, that has implemented them):Up to here is the "normal" design, where every
A
can be related to zero, one or manyB
and everyB
can be related to zero, one or manyA
.The "total participation" restriction needs constraints in the reverse order (from
A
andB
respectively, referencingR
). HavingFOREIGN KEY
constraints in opposite directions (from X to Y and from Y to X) is forming a circle (a "chicken and egg" problem) and that's why we need one of them at least to beDEFERRABLE
. In this case we have two circles (A -> R -> A
andB -> R -> B
so we need two deferrable constraints:Then we can test that we can insert data. Note that the
INITIALLY DEFERRED
is not needed. We could have defined the constraints asDEFERRABLE INITIALLY IMMEDIATE
but then we'd have to use theSET CONSTRAINTS
statement to defer them during the transaction. In every case though, we do need to insert into the tables in a single transaction:Tested at SQLfiddle.
If the DBMS does not have
DEFERRABLE
constraints, one workaround is to define theA (bid)
andB (aid)
columns asNULL
. TheINSERT
procedures/statements will then have to first insert intoA
andB
(putting nulls inbid
andaid
respectively), then insert intoR
and then update the null values above to the related not null values fromR
.With this approach, the DBMS does not enforce the requirements by DDL alone but every
INSERT
(andUPDATE
andDELETE
andMERGE
) procedure has to be considered and adjusted accordingly and users have to be restricted to use only them and not have direct write access to the tables.Having circles in the
FOREIGN KEY
constraints is not considered by many the best practice and for good reasons, complexity being one of them. With the second approach for example (with nullable columns), updating and deleting rows will still have to be done with extra code, depending on the DBMS. In SQL Server for example, you can't just putON DELETE CASCADE
because cascading updates and deletes are not allowed when there are FK circles.Please read also the answers at this related question:
How to have a one-to-many relationship with a privileged child?
Another, 3rd approach (see my answer in the above mentioned question) is to remove the circular FKs completely. So, keeping the first part of the code (with tables
A
,B
,R
and foreign keys only from R to A and B) almost intact (actually simplifying it), we add another table forA
to store the "must have one" related item fromB
. So, theA (bid)
column moves toA_one (bid)
The same is done for the reverse relationship from B to A:The difference over the 1st and 2nd approach is that there are no circular FKs, so cascading updates and deletes will work just fine. The enforcement of "total participation" is not by DDL alone, as in 2nd approach, and has to be done by appropriate procedures (
INSERT/UPDATE/DELETE/MERGE
). A minor difference with the 2nd approach is that all the columns can be defined not nullable.Another, 4th approach (see @Aaron Bertrand's answer in the above mentioned question) is to use filtered/partial unique indexes, if they are available in your DBMS (you'd need two of them, in
R
table, for this case). This is very similar to the 3rd approach, except that you won't need the 2 extra tables. The "total participation" constraint has still to be applied by code.