As you can see here, in OrderInfo and OrderNumber, I have a relationship set. But I'm not sure what kind. I was hoping to set a relationship so if I deleted an OrderNumber, it would cascade delete the corresponding record in OrderInfo, but it won't let me. It gives me this nasty '
can't cascade delete on identity column
error, so after removing cascade delete, it let me save it appropriately. I'm not sure what key to key means, but I know key to infinity is one to many?
It looks like your model is a one-to-one relationship between OrderNumber and OrderInfo, with OrderInfo.OrderID as the OrderInfo primary key and as a foreign key referencing OrderNumber.OrderID.
The error messages suggests you have defined OrderInfo.OrderID as an IDENTITY column. It should not be IDENTITY - the assigned OrderID value from thre related OrderNumber row should be used when inserting the related OrderInfo row to maintain the foreign key relationship. That should allow you to specify DELETE CASCADE.
If you want OrderInfo.OrderID to be a foreign key that references OrderNumber.OrderID, then you have to declare it just as INTEGER not as an INTEGER IDENTITY. If it were an identity, you couldn't assign to it the actual value of the corresponding OrderID that you would have retrieved after inserting to OrderNumber.
That is, you should declare your tables like:
CREATE TABLE Customer
(
CustomerID integer IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(100),
LastName varchar(100)
) ;
CREATE TABLE Consultants
(
ConsultantID integer IDENTITY(1,1) PRIMARY KEY,
EmployeeID integer /* REFERENCES Employees(EmployeeID) */
) ;
CREATE TABLE OrderNumber
(
OrderID integer IDENTITY(1,1) PRIMARY KEY,
CustomerID integer REFERENCES Customer(CustomerID) ON DELETE NO ACTION, /* Referenced CustomerID cannot be deleted */
ConsultantID integer REFERENCES Consultants(ConsultantID) ON DELETE NO ACTION /* Referenced ConsultantID cannot be deleted */
) ;
CREATE TABLE OrderInfo
(
OrderID integer NOT NULL REFERENCES OrderNumber(OrderID) ON DELETE CASCADE,
/* My assumption: one order can have several items
If that were not the case, the following column could be ommitted.
Otherwise, just put all the [OrderInfo] columns in [OrderNumber] table,
and just call it [Order].
*/
SomeOtherColumnSuchAsLineNumber integer NOT NULL,
/* The actual PK should be a composite */
PRIMARY KEY (OrderID, SomeOtherColumnSuchAsLineNumber),
/* Rest of columns */
Cost decimal(12,2),
Description varchar(max)
) ;
-- etc.
We insert customers, consultants and two orders, with two OrderInfo each (I've assumed an order can have more than one item, and added an extra column to sort them).
INSERT INTO Customer(FirstName, LastName)
OUTPUT inserted.CustomerID
VALUES('John', 'Doe') ;
GO
| CustomerID | | ---------: | | 1 |
INSERT INTO Consultants(EmployeeID)
OUTPUT inserted.ConsultantID
VALUES (1), (2);
GO
| ConsultantID | | -----------: | | 1 | | 2 |
INSERT INTO OrderNumber(CustomerID, ConsultantID)
OUTPUT inserted.OrderID
VALUES (1,1), (1,2) ;
GO
| OrderID | | ------: | | 1 | | 2 |
INSERT INTO OrderInfo
(OrderID, SomeOtherColumnSuchAsLineNumber, Cost, Description)
VALUES
(1, 1, 100.00, 'Item costing 100.00'),
(1, 2, 99.99, 'Item costing 99.99'),
(2, 1, 200.00, 'Item costing 200.00'),
(2, 2, 199.99, 'Item costing 199.99') ;
GO
4 rows affected
At this point, if you do have for rows in OrderInfo:
SELECT
* -- Don't do this in production, just in demo
FROM
OrderInfo
ORDER BY
OrderInfo.OrderID, OrderInfo.SomeOtherColumnSuchAsLineNumber ;
GO
OrderID | SomeOtherColumnSuchAsLineNumber | Cost | Description
------: | ------------------------------: | :----- | :------------------
1 | 1 | 100.00 | Item costing 100.00
1 | 2 | 99.99 | Item costing 99.99
2 | 1 | 200.00 | Item costing 200.00
2 | 2 | 199.99 | Item costing 199.99
If you delete now from OrderNumber one row, the change propagates to OrderInfo
DELETE FROM
OrderNumber
WHERE
OrderID = 2;
GO
1 rows affected
-- We deleted from OrderNumber OrderID = 2, it won't appear here
SELECT
* -- Don't do this in production, just in demo
FROM
OrderInfo
ORDER BY
OrderInfo.OrderID, OrderInfo.SomeOtherColumnSuchAsLineNumber ;
GO
OrderID | SomeOtherColumnSuchAsLineNumber | Cost | Description
------: | ------------------------------: | :----- | :------------------
1 | 1 | 100.00 | Item costing 100.00
1 | 2 | 99.99 | Item costing 99.99
You can check everything at dbfiddle here
Before worrying about how to get passed this error, you should consider that perhaps this isn't a technical problem, but instead a logical problem. Looking at the data model, I question why you are splitting OrderInfo and OrderNumber in the first place. If they truly are 1-to-1, then what is gained by splitting them? Is it that sometimes you won't have Cost and Description and are normalizing this out to avoid having NULLs for those two columns? Assuming that you won't have many more fields than those already shown – OrderID, ConsultantID, CustomerID, OrderInfo, and OrderNumber– then most likely (i.e. without knowing more about the overall requirements) you would be far better off consolidating those fields into a single OrderInfo table, and if one or more of those fields needs to accept NULL then so be it.
There are, of course, valid situations for splitting a logical entity into multiple tables (for an example, please see my answer to the following DBA.SE question: SQL Server 2014 compression and maximum row size). This situation, however, does not appear (so far) to be one of them.
You mentioned, in a comment, that you had other, similar places that were 1-to-1 relationships
with both of them identical columns and identical name
and were wondering how to determine which should have the IDENTITY and which shouldn't. I think this is a good opportunity to go back and review those tables and think about whether they truly should be separate or if they should be consolidated. If they truly should be kept as separate tables, then if a set of related tables are all properties of the same logical entity, then only one (whichever is considered the main or primary table) should have an IDENTITY (this is what is shown in my linked answer above). But if they are separate entities then they can each have their own IDENTITY.
On a related note, I find it odd that the PK of the Department table is EmployeeID. Should that instead be DepartmentID?
No there is nothing wrong in linking two identical primary keys in different tables but the problem arises when the data type is declared as identity.
When you declare some field as identity then the value is auto incremented and is decided by the seed if provided. Even though the value are in sync sql server will not allow you to go ahead with this as pointed out by Dan.
Use INT and it will not be an issue.
