I have always enjoyed writing about three topics Constraint and Keys, Backup and Restore and Datetime Functions.
Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.
-- Primary Key Constraint upon Table Created Method 1
USE AdventureWorks
GO
CREATE TABLE ConstraintTable
(ID INT CONSTRAINT Ct_ID PRIMARY KEY, ColSecond INT)
GO
--Clean Up
DROP TABLE ConstraintTable
GO
-- Primary Key Constraint upon Table Created Method 2
USE AdventureWorks
GO
CREATE TABLE ConstraintTable
(ID INT, ColSecond INT,
CONSTRAINT Ct_ID PRIMARY KEY (ID))
GO
--Clean Up
DROP TABLE ConstraintTable
GO
-- Primary Key Constraint after Table Created Method 3
USE AdventureWorks
GO
CREATE TABLE ConstraintTable
(ID INT, ColSecond INT)
GO
-- Primary Key Column must not allow NULL
ALTER TABLE ConstraintTable
ALTER COLUMN ID INT NOT NULL
GO
ALTER TABLE ConstraintTable
ADD CONSTRAINT Ct_ID PRIMARY KEY (ID)
--Clean Up
DROP TABLE ConstraintTable
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
Hello Sir,
I am an aspiring DBA can u pls suggest me a book from where i can learn to administer both SQL Server 2005 and Oracle.
Thanking you in advance.
Amit
Hello Sir,
Wish U Very Very Happy Diwali
I Am Ashutosh Here Sir I Also Become a MCTS.Net & DBA so please can U Suggest Me Which books i should be read
Thank You Sir
Ashutosh Bagul
ur Advice is Valuable…DBA
ur Advice is Valuable…DBA
Do let me know presently m working in a bank as a Datavbase Administrator for Sql server 2005 though i dont have very much to do even nothing to explore my skills here …/…. i m trying to move on to devlopement company as soon as possible here i feel like standstill ……meanwhile do u think that i shud carry on with this job look though i m learning ———–indexing,Physical and logical data modeling—–Performance tunning of Queris and Database ,clustring knowledge.Troubleshooting of Queris and Procedures. at my own skills…..m only doing some backup statgies automating backup’s and maintainance plan and Using Dbcc commands replication and mirroring etc…
Do you think that it ll count as a experience for switching off to next company….
Please Plea advice me ……
Vaibhav mathur
Thanks :-)
it is very useful to beginers
hello sir
I have plan to do some project in inventary model for this I have created my database in sql 2005 as by back-end and c#.net is my front end for this i would like to know difference between the sql scripting language and ordinary database language plz help me…………..
hello pinal sir,
i want to add a column of NOT NULL type using ALTER TABLE statement
how can i do it.
also i want to add this column without default constraint
waiting for ur reply
thanks in advance!!!!!!!
@Zubair.
There are only few situations where you can add a column
1. With not null contraint
2. With no default constraint.
First of all this does not even make sense to do something like this, it will definitely not have any business value.
Anyways here are these two ways to do it.
1. Definitely you can add a column to a table , with not null and not default constraint, but the table has to be empty. There has to be no data in the table, this can be done with T-SQL Script.
2. If you have data in the table and you still want to add a column with not null constraint and with out default constraint. You can add an identity column, unfortunately you cannot add an identity column with T-SQL script , you have to use SSMS.
If you say, No, I have data already in the table, and still want to add a column to the table with not null, not default constraint, then you decide what values should go into previous records for this new column ???
Think logically
Hope this helps,
IM.
[email removed]
Hi Ajay,
here you go……………
CREATE TABLE dbo.card_swipe (
Card_id INT NOT NULL,
Swipe_dttm DATETIME NOT NULL,
CONSTRAINT pk_ card_swipe PRIMARY KEY (Card_id, Swipe_dttm)
)
An entry is added to this table each time a card is swiped. We don’t know whether it is an entry or an exit. We do know that the first entry for a card will be an entry, the next will be an exit, the one after that will be an entry and so on.
I want you to formulate 2 queries:
Query 1:
Should list card_id, swipe_dttm and an indicator whether the row is an entry or an exit
So if data is
Card_id, swipe_dttm
1, 2015-05-04 3pm
1, 2015-05-04 4pm
2, 2015-05-04 4pm
1, 2015-05-04 5pm
2, 2015-05-04 6pm
1, 2015-05-04 5:30pm
3, 2015-05-04 4pm
It should return
Card_id, swipe_dttm,swipe_type
1, 2015-05-04 3pm,entry
1, 2015-05-04 4pm,exit
2, 2015-05-04 4pm,entry
1, 2015-05-04 5pm,entry
2, 2015-05-04 6pm,exit
1, 2015-05-04 5:30pm,exit
3, 2015-05-04 4pm,entry
The 2nd query would display only 1 row per entry (& optional) exit pair and display the entry and optional exit time in the same row. So the result for the above example would be
Card_id, entry_dttm,exit_dttm
1, 2015-05-04 3pm,2015-05-04 4pm
2, 2015-05-04 4pm, 2015-05-04 6pm
1, 2015-05-04 5pm, 2015-05-04 5:30pm
3, 2015-05-04 4pm,NULL
If you want to create Primary Keys on all tables in SQL Server that don’t have PKs, this SQL will generate all the PK create code.
I use this on databases i inherit that are missing lots of keys…
Hope this is of use!
@nickhac
select * ,
‘ALTER TABLE ‘ + SchemaName + ‘.’ + Tablename + ‘ ADD CONSTRAINT PK_’ + Tablename + ‘ PRIMARY KEY CLUSTERED (‘ +Table_Column_FirstWithIDInName + ‘)’ ScriptToCreatePrimaryKey
from (
SELECT
OBJECT_SCHEMA_NAME(OBJECT_ID) SchemaName, OBJECT_NAME(OBJECT_ID) AS Tablename,
(SELECT top 1 Column_Name FROM INFORMATION_SCHEMA.COLUMNS where table_name = T.name and column_name like ‘%ID%’ order by Ordinal_Position) Table_Column_FirstWithIDInName
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,’TableHasPrimaryKey’) = 0 AND type = ‘U’
) TblsWithoutPK