Tag: T-SQL

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One thing that helps ensure your data is intact and doesn’t get out of synch in a relational database is a foreign key. You ought to have these as a part of your design, ensuring that a linkage between a parent and child cannot be broken.

This post looks at adding a FK to an existing table. I’ve written about how to do this in the CREATE TABLE statement in another post.

I have two tables set up: Contacts and Status. Both of these have a StatusID column in them. The Status table contains the lookup values, and these are stored in the child table, Contacts.

To add the foreign key, I add a constraint with the ALTER TABLE ADD CONSTRAINT syntax. After this, I use FOREIGN KEY to list the column(s) and then the REFERENCES phrase to point out the parent table and column.

The example is shown here:

ALTER TABLE dbo.Contacts
   ADD CONSTRAINT FK_Contacts_Status_StatusID FOREIGN KEY (StatusID)
      REFERENCES dbo.Status (Statusid)
;

This will give me  a FK that enforces the values in Contacts as existing in Status.

SQLNewBlogger

I had to do this recently and decided to quickly write this up as I had to look up the syntax to be sure I remembered it correctly. Then it took me about 5 minutes to produce this.

It took me almost as long to see if I’d already written about altering a table with a FK.

Do this for your career, and to show interviewers that you know how to handle common data referential integrity tasks.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a question recently about getting the code in a stored procedure. I’ve become dependent on SQL Prompt, which gives me a nice preview of code when I see a proc:

The main way I’ve gotten code is right clicking a proc in Object Explorer and clicking Modify. That opens up an ALTER statement for the proc.

However, there’s one more way to do this: sp_helptext.

Not the best solution, but it gives me a quick look from SQL without futzing through a big list in the OE.

SQLNewBlogger

This is a quick example of some knowledge that I can use in my regular work. When someone asked the question, I knew the code for procs was stored somewhere, but I wasn’t sure what the proc to get this was. I tried sp_help, but that wasn’t right, so I had to go look up sp_helptext.

This took about ten minutes to write.

I wrote about sequences in an editorial recently and decided to start using them. Creating one turned out to be surprisingly easy.

I had a table I’d been logging some data in, with a PK, but no natural key or identity bound to the table. Instead, this was low volume (1x per day) and I just manually adjusted the PK value.

I decided to create a sequence. As I did this, I found it interesting and consulted the BOL page for some ideas on the options.

The first part is simple. A name and datatype.

CREATE SEQUENCE dbo.MyKey as INT

That makes sense. Next, I need a starting value. In my case, I had 7 values in the table, so I added this:

START WITH 8

That gets me what I need. The last part I added was the INCREMENT BY clause. In my case, 1 works fine. My code:

The last query is how you get the next value. The NEXT VALUE FOR phrase can be used in various places, but that’s for another day.

If I needed to bind values, I could use the MINVALUE or MAXVALUE, which I’ll look at in a different post. I could also control caching and cycle values if I needed to.

SQLNewBlogger

I actually started a post on binding this to a column and then decided to add this short post in about 5 minutes.