October 28, 2021 at 3:37 pm
I work on sql server 2012 i see constrain but i don't know what is meaning
ALTER TABLE [Parts].[TradeCodes] ADD CONSTRAINT [UC_PartCode] UNIQUE NONCLUSTERED
(
[PartID] ASC,
[CodeTypeID] ASC,
[PartLevel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
so what is benefit from code above
October 28, 2021 at 4:15 pm
It's creating both a UNIQUE CONSTRAINT and a UNIQUE INDEX. Firstly, this means that 2 (or more) rows cannot exist with the same values for the columns specified; in this case partid, CodeTypeID and PartLevel. If a second row is attempted to be inserted (or an existing row updated) that would match an existing row, then the CONSTRAINT would fail, and the DML statement would not be committing.
The other part is that the above will create an INDEX as well, meaning that this'll help the data engine search the table when you're looking for specific combinations of the columns above. Perhaps have a look here for an introduction on indexes.
As for the benefits? Well, indexing helps with speed, as I mentioned, and the UNIQUE property is likely enforcing specific business logic (difficult to know what with the column names), but it would be like a website not allowing multiple users to have the same user name.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 30, 2021 at 12:36 am
To summarize what Thom has so correctly stated, it's "DEI", which stands for "Declared Entity Integrity" and, along with "DRI" (Declared Referential Integrity), it forms the backbone of what properly normalized databases are all about.
There are a whole lot of articles on the subject out on the Internet including some from Microsoft where they specifically talk about "Primary Key Constraints" (DEI) and Foreign Key Constraints (DRI). You really can't have a relational database without them and the articles are better written that I could ever do on a forum post or even in an article.
With that, I'll say to do a search for "Entity Integrity in databases" and "Referential Integrity in databases" to start. Then search for "Primary Key Constraints in SQL Server" and "Foreign Key Constraints in SQL Server".
A super simple beginner's article on the subject can be found at the following URL. It also quickly explains the differences between transactional and reference data as well as a very brief, high level explanation of what "Normalization" is (which is a HUGE subject all of it's own).
Don't let it be the only article you read though. It's just an introduction to a very important and much larger subject.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2021 at 3:02 pm
Some tables have multiple unique 'keys'. One should normally map to a 'primary key' constraint. Any other keys should map to 'unique' constraints. Subtle differences are 'primary key' constraints can not contain any null values and there can be just one 'primary key' constraint per table. 'Unique' constraints can contain at most one null value and there can be many 'unique' constraints per table.
Query optimisers love uniqueness, regardless of whether they are defined via constraints or directly using unique indexes. Well worth the discipline of identifying all keys during database design and then enforcing them as constraints within the database schema.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply