SQL 2016.2 Database Creation Error

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL 2016.2 Database Creation Error

swilliams
Hi,

When setting up a new MS SQL Orbeon Forms database using the 2016.2 setup script, I received the following error:

Msg 1776, Level 16, State 0, Line 57
There are no primary or candidate keys in the referenced table 'orbeon_form_data' that match the referencing column list in the foreign key 'FK__orbeon_i___data___1367E606'.
Msg 1750, Level 16, State 0, Line 57
Could not create constraint or index. See previous errors.

From what I can tell this is due to dbo.orbeon_form_data.id not being created as a PRIMARY KEY. For anyone else that comes across this issue, simply add PRIMARY KEY to the end of the dbo.orbeon_form_data.id field in the table create portion. The script will now look like this:

CREATE TABLE orbeon_form_data (
    id                  INT IDENTITY(1, 1) PRIMARY KEY ,
    created             DATETIME                ,
    last_modified_time  DATETIME                ,
    last_modified_by    NVARCHAR(255)           ,
    username            NVARCHAR(255)           ,
    groupname           NVARCHAR(255)           ,
    app                 NVARCHAR(255)           ,
    form                NVARCHAR(255)           ,
    form_version        INT             NOT NULL,
    document_id         NVARCHAR(255)           ,
    draft               CHAR(1)         NOT NULL,
    deleted             CHAR(1)         NOT NULL,
    xml                 XML,
);

Happy to be told I'm wrong, but from the error message generated that made sense to me. After I added the PRIMARY KEY value to the script, it completed successfully.

Good luck!

Stewart.
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2016.2 Database Creation Error

Alessandro  Vernet
Administrator
Hi Stewart,

Having orbeon_form_data.id as a PRIMARY KEY definitely makes sense; we'll do that change, and in fact this is the way it is on other databases, so this was more of an oversight on our part.

However, I am curious about the error you were getting, since, as you can imagine, we didn't get it on our side. This was just went when running the sqlserver-2016_2.sql on an empty database? If it is, what version of SQL Server are you using?

https://github.com/orbeon/orbeon-forms/blob/master/form-runner/src/main/resources/apps/fr/persistence/relational/ddl/sqlserver-2016_2.sql

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

RE: SQL 2016.2 Database Creation Error

swilliams

Hi Alex,

 

Yes it was running on a new blank database, SQL Server version 2014 with SP1.

 

Cheers!

 

Kind regards,

 

Stewart Williams

 

http://www.sbsystems.com.au/images/i_r2_c2.jpg

p:  1300 798 717

m: 0413 460 941

f:  07 3350 3397

e:  [hidden email]

w: www.sbsystems.com.au

 

 

Open Cabler Registration No: T42433

Security Adviser and Equipment Installer: 4004280

 

cid:image002.png@01D08E2F.470EE530    cid:image002.png@01CE5DFA.767FFEB0    CSA_emailNew Picture

 

This e-mail, including all attachments, may be confidential or privileged.  Confidentiality or privilege is not waived or lost because this e-mail has been sent to you in error.  If you are not the intended recipient any use, disclosure or copying of this e-mail is prohibited.  If you have received it in error please notify the sender immediately by reply e-mail and destroy all copies of this e-mail and any attachments.  All liability for direct and indirect loss arising from this e-mail and any attachments is hereby disclaimed to the extent permitted by law.

 

From: Alessandro Vernet [via Orbeon Forms community mailing list] [mailto:ml-node+[hidden email]]
Sent: Wednesday, 26 October 2016 11:01 AM
To: Stewart Williams <[hidden email]>
Subject: Re: SQL 2016.2 Database Creation Error

 

Hi Stewart,

Having orbeon_form_data.id as a PRIMARY KEY definitely makes sense; we'll do that change, and in fact this is the way it is on other databases, so this was more of an oversight on our part.

However, I am curious about the error you were getting, since, as you can imagine, we didn't get it on our side. This was just went when running the sqlserver-2016_2.sql on an empty database? If it is, what version of SQL Server are you using?

https://github.com/orbeon/orbeon-forms/blob/master/form-runner/src/main/resources/apps/fr/persistence/relational/ddl/sqlserver-2016_2.sql

Alex

--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet

 


If you reply to this email, your message will be added to the discussion below:

http://discuss.orbeon.com/SQL-2016-2-Database-Creation-Error-tp4661889p4661901.html

To unsubscribe from SQL 2016.2 Database Creation Error, click here.
NAML

Reply | Threaded
Open this post in threaded view
|

RE: SQL 2016.2 Database Creation Error

Alessandro  Vernet
Administrator
Stewart, interesting, this is the exact same version we've been using for most of our testing. I'll double check this, and anyhow add the PRIMARY KEY.

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

RE: SQL 2016.2 Database Creation Error

Alessandro  Vernet
Administrator
For reference, this fix will be included in 2016.2.2 PE, and future releases.

https://github.com/orbeon/orbeon-forms/issues/2973

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

RE: SQL 2016.2 Database Creation Error

swilliams

Awesome, glad I could contribute J

 

Kind regards,

 

Stewart Williams

 

http://www.sbsystems.com.au/images/i_r2_c2.jpg

p:  1300 798 717

m: 0413 460 941

f:  07 3350 3397

e:  [hidden email]

w: www.sbsystems.com.au

 

 

Open Cabler Registration No: T42433

Security Adviser and Equipment Installer: 4004280

 

cid:image002.png@01D08E2F.470EE530    cid:image002.png@01CE5DFA.767FFEB0    CSA_emailNew Picture

 

This e-mail, including all attachments, may be confidential or privileged.  Confidentiality or privilege is not waived or lost because this e-mail has been sent to you in error.  If you are not the intended recipient any use, disclosure or copying of this e-mail is prohibited.  If you have received it in error please notify the sender immediately by reply e-mail and destroy all copies of this e-mail and any attachments.  All liability for direct and indirect loss arising from this e-mail and any attachments is hereby disclaimed to the extent permitted by law.

 

From: Alessandro Vernet [via Orbeon Forms community mailing list] [mailto:ml-node+[hidden email]]
Sent: Thursday, 27 October 2016 3:08 AM
To: Stewart Williams <[hidden email]>
Subject: RE: SQL 2016.2 Database Creation Error

 

For reference, this fix will be included in 2016.2.2 PE, and future releases.

https://github.com/orbeon/orbeon-forms/issues/2973

Alex

--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet

 


If you reply to this email, your message will be added to the discussion below:

http://discuss.orbeon.com/SQL-2016-2-Database-Creation-Error-tp4661889p4661906.html

To unsubscribe from SQL 2016.2 Database Creation Error, click here.
NAML