0

I have quite the SQL conundrum.

I am a junior programmer in their first co-op position and I have been given a database with three tables. There are no primary keys on any of the three tables. These tables have no initial relationships with each other. This is how the database is (roughly) designed.

Table 1     Table2      Table3
========    ========    ========    
SSerial     BSerial     MSerial
B1          data2       data2
B2          data3       data3
B3          data4       data4
M1          data5       data5
M2          data6       data6
M3          data7       data7

SSerial, BSerial and MSerial have unique alphanumeric values that will never be repeated(nvarchar(MAX)).

BSerial's values can be found in B1, B2 and B3 of Table 1.

MSerial's values can be found in M1, M2 and M3 of Table1.

The end goal of the application I am working on is allowing the client to search by the unique SSerial number in Table 1, then view the BSerial values that are in B1, B2 and B3. They will also be able to do the same with MSerial's values that are in M1, M2 and M3.

I have tried implementing a primary key so that I may add a relationship between Table1 and the other tables, but with no avail.

When I attempted to create a primary key:

ALTER TABLE dbo.Table2
ADD Table2_ID int NOT NULL IDENTITY (1,1);

ALTER TABLE dbo.Table2
ADD CONSTRAINT PK_Table2_ID PRIMARY KEY (Table2_ID, BSerial);

I was blessed with the following error message:

Msg 1919, Level 16, State 1, Line 1
Column 'BSerial' in table 'Table2' is of a type that is invalid for use as a key column in an index.

Additional info:

  1. SSerial, BSerial and MSerial are NOT NULL and are of type NVARCHAR(MAX).
  2. B1, B2, B3, M1, M2 and M3 ALLOW NULLS, as there will not always be three BSerial's or MSerial's associated to SSerial. They are of type NVARCHAR(MAX).
  3. Recreating the database from scratch is not ideal as there are thousands of rows of data in each table, but it can be done.

This is my first Stack Overflow question and if more information is needed I am more than welcome to elaborate. Thank you for your time!

Erich. L
  • 17
  • 6
  • 3
    There are size limits on an index. https://technet.microsoft.com/en-us/library/ms191241(v=sql.105).aspx Try to use data types smaller than nvarchar(max). Do you really need virtually unlimited text in these fields? – Jeremy Jan 31 '17 at 16:59
  • I strongly believe I do. The Serial number has a fairly large length, I think on average 45-60 characters, but that could grow. When we ship to the client they will be inserting 900+ parts and their information into the database per day, I would have to check with my supervisor if their length could change over time. – Erich. L Jan 31 '17 at 17:06
  • 45-60 characters is far, *far* less than the capacity of `nvarchar(max)`. See [What is the maximum characters for the nvarchar(max)?](http://stackoverflow.com/questions/11131958/what-is-the-maximum-characters-for-the-nvarcharmax). It would be much better to change this to the current actual maximum plus a bit. If more room is ever needed, then it shouldn't be too hard to increase the capacity at that time. – John Bollinger Jan 31 '17 at 17:20

2 Answers2

1

Change the data type from NVARCHAR(MAX) to NVARCHAR(450).
An index's key cannot exceed a total size of 900 bytes.

Quethzel Diaz
  • 621
  • 1
  • 11
  • 26
0

I would recommend a redesign of your db, but if thats not practical, you do not need primary keys to accomplish your goal. For the code below to work the BSerial and MSerial values must be an exact match in table 1. 

    select * from table1 t1
left join table2 t2 on t1.b1 = t2.bserial
left join table2 t3 on t1.m1 = t3.mserial
where t1.sserial = 'some value'
E LaRoche
  • 1,106
  • 1
  • 7
  • 8