This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
1) I'm creating table SubjectWord with one entity property: 'Word' When updating I get this error: The following updates could not be carried out: SubjectWord: Add table SubjectWord to the database - Column 'Word' in table 'SubjectWord' is of the type that is invalid for use as a key column in a index. Could not create constraints. See previous errors.
2) When I'm creating lightspeed context, I'm setting a IdentityMethod.IdentityColumn in code. but I was watching created tables in db and Id column has set property 'IsIdentity' to false. Do I have to setup something to lightspeed designer generate it, or do I have to set it manually?
Thanks.
|
|
|
1. This is probably because Word is an unlimited length text field with a unique validation. LightSpeed tries to translate unique validations to unique database constraints. But some databases (SQL Server at least) cannot apply unique (index) constraints to unlimited text fields. The fix is to set a length validation on the Word property, even if it's a vacuous one like <= 3999. 2. The designer isn't aware of your code or config file settings, so if you want to the designer to create the Id column as an identity column, you must set Identity Method to IdentityColumn in the designer. This can only be done at the entity level, so you would need to do it on each entity in the designer. Which we discourage because it makes it hard to change to another identity method if you later change your database design or target a database which doesn't have identity columns. So you will probably need to set it manually. Sorry. (We know this is a bit irritating, but we recommend against using identity columns because they defeat batching, which is why we don't feel a great compunction to facilitate this!) |
|
|
1. Solved, thanks. 2. I'm for using of KeyTable, but I need insert a lot of data by SQL script manually and how can I get the Id? |
|
|
[quote user="Pooik"] 2. I'm for using of KeyTable, but I need insert a lot of data by SQL script manually and how can I get the Id? [/quote] When you read an Id, how do you raise it? Is there some system, or can I just increment by one? How is it made by lightspeed? He reads it and updates it? |
|
|
You can manually reserve a block from the KeyTable as part of your script. Assuming you are using the default KeyTable definition, you basically do a SELECT NextId FROM KeyTable to get the beginning of the block, then an UPDATE KeyTable SET NextId = NextId + n (where n is at least the number of records you're going to insert manually). Then you can assign IDs from the range of selected_value to selected_value + n - 1. For example, if the SELECT returned 1000 and n was 500, your reserved block would be 1000-1499. Note if you're doing this while the system is live you will want to use WITH (UPDLOCK) and a transaction to ensure that the SELECT and UPDATE are atomic. This is basically what LightSpeed does to "read and raise" IDs. In our case, because we don't know at request time how many records are eventually going to be inserted, n is the IdentityBlockSize rather than a number of records. This is usually greater than 1 (the default is 10), allowing us to efficiently reserve a block of IDs rather than getting them one at a time. Obviously, the viability of this will depend on how you're getting the data that you're inserting by SQL script. If you're being given a bunch of INSERT statements to run, then munging them to add a manually generated ID is no fun, and the IdentityColumn strategy looks more attractive. If, on the other hand, you're receiving a CSV file or something, and generating the INSERT statements yourself, then allocating IDs and adding them to the INSERT statement adds relatively little complexity, which means the KeyTable strategy remains viable. You'll need to make a judgment call about this based on your specific scenario. |
|