4

enter image description here

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?

  • If there is a one-to-one correspondence between those tables, why not make them one table? – Dave Costa Jul 13 '17 at 23:37
  • Did you find a design that was good for your use-case? – joanolo Jul 18 '17 at 18:26
7

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.

7

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

  • I suppose the reason I set it as identity was so that it would auto number. If I remove the identity portion of it, do I have to fill in the order ID myself then? – user18139 Jul 8 '17 at 14:02
  • Yes, that's it. See example in the answer. First: use OUTPUT to retrieve the OrderID value of the inserted row in OrderNumber, and use this value to insert in OrderInfo. That's exactly the same thing you do when you give values to the CustomerID or ConsultantID columns. – joanolo Jul 8 '17 at 14:06
  • 1
    So how exactly do I know what should be an identity and what shouldn't in my tables? Because there are other places where I just have one to one relationship with both of them identical columns and identical name – user18139 Jul 8 '17 at 14:07
  • 1
    It is an identity if it can be any arbitrary number that can be referenced from other tables. It shouldn't be if is a number you've had to look up from another table. So, the referenced columns can be IDENTITY, the referencing ones must not. – joanolo Jul 8 '17 at 14:10
  • Also, I think the SQL standard specifies that only one column per table can be an GENERATED BY IDENTITY. Some databases can have several columns which are AUTO_INCREMENT or SERIAL or take its default values from a SEQUENCE, which do not impose this restriction. – joanolo Jul 8 '17 at 14:19
4

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?

  • 1
    To answer your question, I'm very new to using SQL. So I was trying to normalize it and split it up as much as possible because I didn't want to consolidate it and make it overly complicated with too many columns. To answer your other question at the very bottom, I put it as employee ID because I was assuming that each employee only has one department that they belong to. So I wanted to directly correlate EmployeeID to the EmployeeID in the employees table. Maybe I don't understand how relationships work just yet – user18139 Jul 10 '17 at 12:45
  • 1
    @user18139 Understood re: being new. I think you have over-normalized this and over-complicated it in the attempt to not over-complicate it ;-). Generally you do not need to separate fields for an entity into different tables unless you have too many columns (and don't even consider this if you have < 50 columns), or the columns are over the approx 8060 row size limit, or there is a radically different usage pattern for 1 or more columns that are large. Stuff like that. But for just a few columns (well under 50)? Combine those into a single table. (continued) – Solomon Rutzky Jul 10 '17 at 16:33
  • 1
    @user18139 For Department, you should name tables for the logical entity that they are. Hence a "Department" table needs to contain data about departments. If you want to map between Employee and Department, then use a 3rd table named EmployeeDepartment or something like that. But if an Employee can only ever be in 1 department, AND you don't need to keep a history of it over time (at least not here), then simply put a DepartmentID column (FK) in the Employee table and change the Department table to contain DepartmentID as the PK, and at least 1 other column to be DepartmentName. – Solomon Rutzky Jul 10 '17 at 16:36
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.