Column level and table level restrictions in sql server?

Question

but. Column level

GO CREATE TABLE Products ( ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY, ProductName VARCHAR(25) ); GO 

b. Table level

 CREATE TABLE Products ( ProductID INT, ProductName VARCHAR(25), CONSTRAINT pk_products_pid PRIMARY KEY(ProductID) ); GO 

Is there a difference between column levels and table level?

Answer 1

Not. It is just a matter of personal taste, how do you apply the restriction.

A primary key constraint is simply a primary key constraint - it always refers to a table (after all: it can contain multiple columns - it cannot be "at the column level").

This is not "column level" once or "table level" in another case - it always remains unchanged.

Just for fun - you can also create a primary key constraint in a third way:

 (CREATE TABLE statement) GO ALTER TABLE dbo.Products ADD CONSTRAINT PK_Products_pid PRIMARY KEY(ProductID) 

and again it will be identical to the two other parameters that you already have.

Answer 2

The first example declares a limit on a string , the second does not. Only simple keys (including one attribute) can be declared in a row, composite keys (involving multiple columns) cannot. But both table level restrictions!

---

There are four logical levels of restriction:

1) Column level:

 CHECK ( ProductID > 0 ) 

2) Line Level:

 CHECK ( Product_start_date < Product_end_date ) 

3) Table level (the following example is not yet supported in SQL Server):

 CHECK ( NOT EXISTS ( SELECT * FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY ProductID ) AS Tally FROM Products AS P ) AS DT1 WHERE Tally > 1 ) ) 

4) Database level (not yet supported in SQL Server):

 CREATE ASSERTION EnterpriseUniqueIds CHECK ( NOT EXISTS ( SELECT * FROM ProductID AS P JOIN Components AS C ON C.ComponentID = P.ProductID ) ); 

---

A key limitation involves comparing different rows in the same table, so this is a table-level limitation.

Answer 3

A bit late for the party, but FYI for others

http://social.technet.microsoft.com/wiki/contents/articles/13744.table-level-vs-column-level-constraints.aspx

Answer 4

There are two ways to define constraints: one is at the column level and the other is at the table level. One can use any of these methods to apply restrictions.