Why I have problem with adding IDENTITY in my table?
-
I want to create Table which name is ARTIKAL. Also I want to set Identity in ID column but every time when I run my code I get this.
Here is the code
CREATE TABLE [dbo].[ARTIKAL]
(
ID int IDENTITY(1,1) NOT NULL,
Naziv char(10) NOT NULL,
Cena decimal (5,2) NOT NULL,
Kolicina int NOT NULL,
); -
That code runs fine on my 2017 and 2019 instances.
-
I fully expected this to fail with the trailing comma (after the "Kolicina" column).
Apparently it's a minor bug embraced as a feature: https://github.com/MicrosoftDocs/sql-docs/issues/4807
-
In the original version, try putting brackets around the column name "Kolicina". It looks like you might have a non-standard character in the name.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
By definition, a table must have a key. You don't have one in your DDL so you need to fix that. You have some misconceptions about RDBMS that you might want to fix. IDENTITY is not a column, in spite of the fact we refer to an identity column when we are talking loosely. It's actually a table property and does not model any attribute of the set of entities which the table represents. This is why you can have only one identity on the table, arithmetic done on it makes no sense, and it basically behaves like a pointer in a sequential file. Actually, in the original Sybase product, it was a pointer. A flaw in the original Sybase compiler also allows you to have a comma at the end of the column declaration. The standard language has to have semicolons terminating statements and does not allow dangling commas. This is how you should write your code.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
Thank you for your answer. Now it´s work. I think that was some kind of bug.
- jcelko212 32090 wrote:
By definition, a table must have a key. You don't have one in your DDL so you need to fix that. You have some misconceptions about RDBMS that you might want to fix. IDENTITY is not a column, in spite of the fact we refer to an identity column when we are talking loosely. It's actually a table property and does not model any attribute of the set of entities which the table represents. This is why you can have only one identity on the table, arithmetic done on it makes no sense, and it basically behaves like a pointer in a sequential file. Actually, in the original Sybase product, it was a pointer. A flaw in the original Sybase compiler also allows you to have a comma at the end of the column declaration. The standard language has to have semicolons terminating statements and does not allow dangling commas. This is how you should write your code.
I make some changes and here is my code. I just get started with learning of SQL.
CREATE TABLE [dbo].[ARTIKAL]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY ,
[Naziv] [char](10) NOT NULL,
[Cena] [decimal] (5,2) NOT NULL,
[Kolicina] [int] NOT NULL,
[OPIS] [NVARCHAR](500)NOT NULL
); - Sajo1510 wrote:
Thank you for your answer. Now it´s work. I think that was some kind of bug.
It's not a bug. There are characters that are not allowed in column names. If you absolutely need to have names that contain these characters, then you need to use the square brackets.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
You're getting there! You still have some basic fundamental flaws in your DDL. In a properly designed RDBMS, the data element names will follow the ISO 11179 naming rules. This is "[role_]<attribute>_<attribute property>" and is fairly straightforward. Because ISO standards are not uniform as to their use of upper and lowercase letters (this is a prejudice toward Latin, Greek and Cyrillic alphabets), you have to be very uniform in their use in identifiers.
Some standards are case insensitive and some are very case sensitive. However, the ISO standards in the Unicode standards all agree that every valid character set must contain a simple Latin alphabet, digits, and a limited set of punctuation marks, which include the_. This was done so that the ISO standard abbreviations for metric measurements could be written in any language. In the early days of programming, identifiers in the language had a fixed length. Over 50 years ago when I was a working Fortran programmer, the length was six characters. You probably don't believe that, but it's true :-). Likewise, COBOL started off with 19 characters and has expanded since then. Generally speaking, you really don't have to worry about the length of an identifier today; write a meaningful name for something that will let the next person has to maintain this code know what this data element means in the data model.
I do not recognize your language (Google told me it was Bosnian!), but it looks like something Slavic. I don't know any Slavic language, in spite of my last name and my grandfather's newspapers in Slovak. However, I can simply look at these data element names and tell that they are not up to ISO standards. They are attributes, but do not have an attribute characteristic.
Finally, the highly proprietary and totally non-relational IDENTITY can never be a primary key by definition. Do you really think you're smarter than Dr. Codd? The Sybase/SQL Server product evolved out of UNIX systems. UNIX file systems were based on sequential files. The way you got to a record in those filesystems was by counting position with in the file. If you want a mental model, imagine that the records coming into the file. They are like cars coming into a parking lot and being put into parking spaces which are sequentially numbered. If you want to identify the car, using the parking space number makes no sense. That number (IDENTITY) is totally dependent on when the car arrived at the parking lot. It is not a property of the car. This is why your insurance number, your government agencies that deal with your car, the manufacturer, etc. depend on the VIN. That's a real identifier!
Things like "_name " ("naziv"??) Are examples of an attribute property. To be a name. something has to be the name of something in particular – an employee, a product, a city, etc. – some kind of entity. .
CREATE TABLE ARTIKAL -- inventory ??
(gtin CHAR(15) NOT NULL PRIMARY KEY, -- universal, industry standard identifier
naziv CHAR(10) NOT NULL, -- product_name??
cena DECIMAL (5,2) NOT NULL, -- unit_price??
kolicina INTEGER NOT NULL, -- inventory_quantity ??
opis NVARCHAR(500) NOT NULL); -- product_description??
Assuming my translations are good, the name of the table should be a plural or collective noun and not a singular noun. Only if you have one and only one product; or perhaps zero, if this is an empty table. A collective noun in English might be "inventory"; I do not know the Polish/Bosnian/whatever for this. But I do know that the identifier should be the "global trade item number" or the GTIN.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 wrote:
...the name of the table should be a plural or collective noun and not a singular noun
If you're going to cite a spec, do it properly with Part, Section, and Paragraph numbers, please.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply