Making a Primary key mixture between characters and numbers

Question

0.00/5 (No votes)

See more:

Hi,

I would like to make a table called student that has a primary key like the following:
STD-1 for the first entry , STD-2 for the 2nd entry, etc.

I tried the following code:

SQL

create table Student
(
    SID int identity (1,1),
    STID as 'SID-'+Cast(SID as Varchar(10)) PERSISTED PRIMARY KEY,
    Name varchar(20) not null,
    City varchar(30) not null,
    Phone varchar(10) not null
);

But it only works with SQL server Express not with the compact edition.

Is there a way to write a code similar to it to work on compact edition?

Posted 7-Sep-13 23:54pm

Sammy FCI

Updated 8-Sep-13 0:52am

André Kraak

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-09-08T00:36:00

Solution 1

SqlCE does not support Computed Columns, so you will have to do this in your external code.

Posted 8-Sep-13 0:36am

OriginalGriff

Comments

Sammy FCI 8-Sep-13 7:04am

Thank you very much

OriginalGriff 8-Sep-13 7:15am

Welcome - sorry it's not the answer you probably wanted! :laugh:

phil.o · Accepted Answer · 2013-09-08T00:51:00

I do not recommend you to use a string column as your primary key.

Why : performance will be better if you keep an int primary key (SID). You can still have a column for your computed value, that can be set unique accross the table => you will keep the desired functionnality. But you will have much more efficient searches and joins on this table with an int primary key.

And to answer to your question, here is the version for SQL Compact:

SQL

create table Student
(
    SID int PRIMARY KEY IDENTITY(1,1),
    STID nvarchar(14)) UNIQUE NOT NULL,
    Name nvarchar(20) NOT NULL,
    City nvarchar(30) NOT NULL,
    Phone varchar(10) NOT NULL
);

But, as it has been told to you, with SQL Server Compact you will have to handle the creation of the STID value in your code, as the database engine cannot handle that for you. Something like:

C#

Student.STID = string.Format("SID-{0}", student.SID);

Hope this helps.