manoool


Hi.....

I want to ask about two thing

and I hope that some one can help me:

1- I want to create apimary key from 6 integer digits that contain two part

the first part is the first two number from the year

the second part is auto generation number from 0000 to 9999

for example: in this year "2007"

the number will be from 070000 to 079999

how can do this

2- the second thing is that when i try to set the identity properity

to 3 columns

this is does not work and the last columns will be saved.

thanks




Re: Primary Key Function

Kent Waldrop No07


Manool,

Your first issue is generally considered bad design because your single field consists of two parts; In addition, it looks like you are physically wanting to store the leading zero character for your field. That will make your field a char datatype instead of a numeric datatype.

If you must have this as your primary key, consider implementing this as three separate columns. The first column would be the last two digits of the year, the second column would be your identity column and the third column would be a computed column based on the other two. This column would be computed as:

convert(char(8), 1000000*column_1 + column2)

The second issue is that you are only allowed to have one identity column per table; therefore, the answer to this question is that a single table cannot have multiple identity columns. What is the purpose that you need three identity columns on a single table






Re: Primary Key Function

manoool

thank you for your answer

you are right in the issue that the field must be char

but you miss understand me

beacuse I want this field (primary Key) to be one column which is acombination of

many value not acombination of 3 columns

and about the second issue :

my project is to build a web system for university

and in one table I have to display the faculties , departments and courses

but the feculties have many departments

and the department have many courses

so I connect between them like this

the faculties ID is 100,200,300,400,.........

the departments ID is 10,20,30,40,.............

the course ID is 1,2,3,4,5,

such that

1- the primary key is the course ID

2- the faculties 100 have the departments from 10 to 90

3- the departments 10 have the courses from 1 to 9

ets...............

and I dont know how to achive this!!!!!!!!!!!

beacuse of this I want 3 identity in one table






Re: Primary Key Function

Kent Waldrop No07

How much experience do you have with database table design Are you basically new to table design





Re: Primary Key Function

manoool

OH may god

why !!!!!!!!!

is my thoughts is very stupid

I don't have much experience with SQL server

but I was use before Access .





Re: Primary Key Function

John Gordon - MSFT

If you absolutely need to concatenate these values for some reason, you could do that. However, you could also use a multi-column primary key. A primary key does not have to consist of only one column, so you don't necessarily have to resort to concatenating all these columns together to create your own key.

http://www.sqlteam.com/forums/topic.asp TOPIC_ID=6136

http://www.webservertalk.com/message988886.html

http://msdn2.microsoft.com/en-us/library/z24kefs8(VS.80).aspx

I hope this helps. There is a natural interpretation of primary key coming from an Access background that is somewhat different from a SQL Server primary key. It is not stupid to bring your experiences through life with you and to try to leverage them as best you can. I think the poster before you was just trying to figure out how much experience you have with SQL Server specifically, so that we can provide you with the right level of instruction without seeming pedantic or obtuse.

Hope this helps,

John






Re: Primary Key Function

manoool

thank you soooo much for your help

i was joking about the stupid issue

and i want to ask about "Kent Waldrop No07" suggestion

I think it will be work

convert(char(8), 1000000*column_1 + column2)

but where i should use it because this field will be identity

so i can't but it in

"Default Value or Binding " property

or in

"Formula" property

and thank you again

Mr. John





Re: Primary Key Function

Kent Waldrop No07

Manool,

It is better if you DON'T do it that way that I wrote. I just gave that as a method if it was something that you absolutely had to do for some reason; I would still consider this a poor design alternative. Also, sorry about the two questions. But I was fearing that you were new to SQL Server and that you would implement that suggestion without really understanding what you were getting into. As John said, it really is better if you implement a multi-column key.

Kent





Re: Primary Key Function

manoool

Thank you for all

you said : John said, it really is better if you implement a multi-column key.

he suggested that for the second problem

and I solved as the follwing:

I put one table contain the faculty name,department ID (identity) and department name

and another table contain the courses information and department ID

Of course, with existing of the other tables such as the section,student........

but no one realy helped me in the first issue

which is

"I want to create apimary key from 6 integer digits that contain two part

the first part is the first two number from the year

the second part is auto generation number from 0000 to 9999

for example: in this year "2007"

the number will be from 070000 to 079999"

you said: Also, sorry about the two questions.

its OK.......

I was joking as I said before

thanks





Re: Primary Key Function

John Gordon - MSFT

create table test(a2 char(8))

create proc makeTestKey(@courseNumber int) as

begin

insert into test(a2) values(right( replicate('0', 7) + convert(char(6), convert(int, Year(GetDate()) % 2000 * 10000 + @courseNumber)), 7))

end

call it like

'exec makeTestKey 20'

there you go.

Pretty sure that won't help in the long run, though. It has (for instance) a built in Y2K bug.






Re: Primary Key Function

manoool

Hi again.......

I want to ask Mr.john

I think that your way will be work

but I want to ask you about it.

create proc makeTestKey(@courseNumber int) as

begin

insert into test(a2) values(right( replicate('0', 7) + convert(char(6), convert(int, Year(GetDate()) % 2000 * 10000 + @courseNumber)), 7))

end

where should i but this procedure

and what is the @courseNumber

is it a column in the test table with identity property

call it like

'exec makeTestKey 20'

where should I but this call

what is the (20) that follow the procedure name

and thank you for your help..........





Re: Primary Key Function

manoool

Please any one can help me





Re: Primary Key Function

John Gordon - MSFT

CourseNumber was the course number you enter, you said you wanted a key to be generated based on course number and year. The year is obtained inside the stored procedure, and the course number is taken on input. CourseNumber is an integer, beyond that the stored procedure does not care. The "20" is the input to the stored proc, it is CourseNumber. You can call the stored procedure wherever you can execute SQL statements. The batch is not designed to solve your problem, it makes the key and inserts it into the table, you could return the key instead if you wanted the stored procedure to do that. This was just a proof of concept, and I expect you to be capable of (at a minimum) modifying the stored procedure to return that value instead of inserting it into a predefined table.

Hope that helps,

John