PK question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

PK question

Author

Topic

RobVG
Starting Member

42 Posts

Posted - 2003-07-25 : 13:57:46

(Newbie question)
I have table(list) with an Identity column and a column for Radio station call letters. There is also a table that contains the same info for stations that are no longer active.

I want to insure that the call letters entered into the delete list are valid(checked against the stationlist) I've gotten into the habit of using the Identity column as the PK. After reading a sqlteam article, would it be better just to use the call letters as the PK?

(I tried making a composite PK from Station_ID,StationName but I couldn't reference it from the delete list. Is it possible to reference a composite PK?)

Rob(a little confused)

On second thought, I want to make sure that both Station_ID and StationName are valid. Suggestions? (drawing a blank)

Edited by - RobVG on 07/25/2003 14:11:40

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-25 : 14:17:44

If the call sign letters are unique (commonly referred to as the natural key), then you should use that column as the PK.

Your identity column should then be discarded entirely.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 14:19:00

I WAS going to suggest that you set up a foreign key that references back to the active, but the realized you probably are going to delete it from that Radio station list. If you don't then you can do that (but then that wouldn't make much sense, and would complicate your queries).

If you wan THAT, let me know.

But I suggest you do this:


CREATE TABLE RadioStations (
	  CallLetters varchar(10)
	, StationName varchar(255)
	, ActiveInd char(1)
	, CONSTRAINT pk_RadioStations 
	  PRIMARY KEY (CallLetters)
	, CONSTRAINT uni_RadioStations UNIQUE (StationName)
	, CONSTRAINT ck_RadioStations CHECK (ActiveInd IN ('Y','N')
) on [PRIMARY]

Brett

8-)

RobVG
Starting Member

42 Posts

Posted - 2003-07-25 : 14:43:55

Both suggestions are a big help.

I think I'll go with jonathan's because I have a VB form that accesses these tables and I would have to go back and change alot of code.

I like your Idea Brett, using only one table with the yes/no check constraint. This would have been easier and cleaner writing a logic statement against.

(What's the "on [PRIMARY]")

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-25 : 14:53:31

quote:

(What's the "on [PRIMARY]")

It just tells it which filegroup to put the table on. Typically, only one filegroup is used which is the MDF file. But you can create additional filegroups, with extension NDF, so that you can put tables on different files and spread them across different drives for performance. ON PRIMARY just means put it on the main file which is the MDF file.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 15:21:03

OK, Since you are leaving the rows in the main table..to keep only valid values in the inactive (and to conform to your model)



CREATE TABLE RadioStations_Active (
	  CallLetters varchar(10)
	, StationName varchar(255)
	, CONSTRAINT pk_RadioStations_Active 
	  PRIMARY KEY (CallLetters)
	, CONSTRAINT uni_RadioStations_Active UNIQUE (StationName)
) on [PRIMARY]

CREATE TABLE RadioStations_InActive (
	  CallLetters varchar(10)
	, StationName varchar(255)
	, CONSTRAINT pk_RadioStations_Active 
	  PRIMARY KEY (CallLetters)
	, CONSTRAINT uni_RadioStations_Active UNIQUE (StationName)
) on [PRIMARY]


ALTER TABLE RadioStations_InActive ADD 
	 FOREIGN KEY 
	(
		CallLetters
	) REFERENCES [RadioStations_Active (
		CallLetters

Brett

8-)

RobVG
Starting Member

42 Posts

Posted - 2003-07-25 : 15:47:16

I'm a little hazy when it comes to one to ones. Does an entry into RadioStations_InActive, CallLetters have to match an existing CallLetters in the RadioStations_Active table or are they independent?

I like your first idea so much I'm wrestling with thoughts of making the changes. I could chop off a page of VB code. 10 mins of "improving" usually means 90 mins of "fixing".

Any reason why you had to use the ALTER TABLE Statement instead of applying the constraint at the table level?

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 15:57:14

quote:

I'm a little hazy when it comes to one to ones. Does an entry into RadioStations_InActive, CallLetters have to match an existing CallLetters in the RadioStations_Active table or are they independent?

Yes, the reference is back to "Active" table so any value in inactive has to be in active, If you don't have a composite key in Inactive it's a 0-1 relationship.

And in Your Case "Active" is a misnomer now....ALL radio stations have to be in there.

quote:

I like your first idea so much I'm wrestling with thoughts of making the changes.

Thanks...

quote:

I could chop off a page of VB code. 10 mins of "improving" usually means 90 mins of "fixing".

Always much tougher to alter the model after...This is why a good design makes life SO much easier when developing...the dba's can protect the developers from themselves in most cases.

quote:

Any reason why you had to use the ALTER TABLE Statement instead of applying the constraint at the table level?

Yeah, being lazy, I let EM gen some sample code of an object that I knew was similar to yours and just mad slight changes..

Good Luck

Come on happy hour 4:00 and counting

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-25 : 16:04:06

Rob-
Remember also the availability of VIEWS as an intermediate modeling layer to shape rowsets in desirable ways. You don't necessarily need to change your table layouts.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 16:10:06

True, and a very good point...

CREATE VIEW AS yourOriginalActive TableName
AS

SELECT * -- Don't do this list the columns
FROM RadioStations
WHERE ActiveInd = 'Y'

CREATE VIEW AS yourOriginalInActive TableName
AS

SELECT * -- Don't do this list the columns
FROM RadioStations
WHERE ActiveInd = 'N'

But if mentioned IDENTITY, I bet the predicates are

WHERE [ID] = 1 (or whatever number)

Brett

8-)

RobVG
Starting Member

42 Posts

Posted - 2003-07-25 : 16:54:31

Actually Jonathan, I think i am using views (in a way) on the VB side.

Using ADO recordset objects I can use a sql statement to get the records from the tables ex.:

RadRs.Open "SELECT StationName FROM StationDelete", gMyconn

VB gathers the radio stations from the "master" station list and then they are compared one by one to the StationDelete list. Only active stations will be placed in the captions of the check boxes on the form (marketing survey)Check boxes are not visible until they have a caption and the rest of the form scales with the addition/subtraction of the check boxes. If a Station is added to the database- it shows up on the app. If a station is on the delete list it won't be shown.

This whole comparison routine could have been avoided by simply checking the "Active" column for a 'Y' or 'N'

For now I made the changes you recommended.

(Thanks Tara for the file Info)

Edited by - RobVG on 07/25/2003 16:56:04

Edited by - RobVG on 07/25/2003 16:58:16

joseph
Starting Member

10 Posts

Posted - 2003-07-26 : 00:12:12

quote:

It just tells it which filegroup to put the table on. Typically, only one filegroup is used which is the MDF file. But you can create additional filegroups, with extension NDF, so that you can put tables on different files and spread them across different drives for performance. ON PRIMARY just means put it on the main file which is the MDF file.

Tara

Does Filegroup with extension NDF automaticaly growth?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources