What to use for SQL Server instead of "Key"?

Question

Hi!

I have a script of MySQL queries that I use and that work.

I'm trying to execute the same queries in Microsoft SQL server and there's one thing I don't understand.

MySql uses "key" to define a key made up of different fields.

What is the way to do the same thing in SQL Server?

Thanks!

-Adeena

Answer 1

You can declare a primary key that consists of multiple columns in TSQL (SQL server's query language)

ALTER TABLE product
    ADD CONSTRAINT prim_prod PRIMARY KEY(product_foo, product_bar)

If you use SQL Server Management studio, you can also achieve this via "Modify Table".

Answer 2

In MySQL, the keyword KEY is simply a synonym for INDEX. The following two are equivalent:

CREATE TABLE foo (
  id     SERIAL PRIMARY KEY,
  ctime  DATETIME,
  KEY ctkey (ctime)
);

CREATE TABLE foo (
  id     SERIAL PRIMARY KEY,
  ctime  DATETIME,
  INDEX ctidx (ctime)
);

In Microsoft SQL Server, the closest equivalent is INDEX. As far as I can tell, to create an index on a column in Microsoft SQL Server, you use CREATE INDEX. You can also create constraints that build indexes as part of a CREATE TABLE statement, but if you just need an index, use CREATE INDEX.

CREATE TABLE foo (
  id     BIGINT IDENTITY PRIMARY KEY,
  ctime  DATETIME
);

CREATE INDEX ctidx ON foo(ctime);

See also documentation on CREATE INDEX.