A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.
A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field[s], then you cannot have two records having the same value of that field[s].
Note − You would use these concepts while creating database tables.
Create Primary Key
Here is the syntax to define the ID attribute as a primary key in a CUSTOMERS table.
CREATE TABLE CUSTOMERS[ ID INT NOT NULL, NAME VARCHAR [20] NOT NULL, AGE INT NOT NULL, ADDRESS CHAR [25] , SALARY DECIMAL [18, 2], PRIMARY KEY [ID] ];
To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists, use the following SQL syntax.
Options
- Primary key can not have null values
- Primary key may contain duplicate values
- Primary key can not be applied for multiple columns
- All of the above
CORRECT ANSWER : Primary key may contain duplicate values
Enter the code shown above:
[Note: If you cannot read the numbers in the above image, reload the page to generate a new one.]
Which statement is true for a PRIMARY KEY constraint?
Primary key defines a realtionship between two tables.
A table in SQL must have a primary key associated with it to uniquely identify its records.
A table in SQL is indexed by default based on its primary key.
Primary key may or may not be unique but can be comprised of multiple fields.
I used the CREATE TABLE AS syntax to merge several columns and encountered the same problem. Here is an AppleScript I wrote to speed the process up.
set databasePath to "~/Documents/Databases/example.db"
set tableOne to "separate" -- Table from which you are pulling data
set tableTwo to "merged" -- Table you are creating
set {tempCol, tempColEntry, permColEntry} to {{}, {}, {}}
set permCol to {"id integer primary key"}
-- Columns are created from single items AND from the last item of a list
-- {{"a", "b", "c"}, "d", "e"} Columns "a" and "b" will be merged into a new column "c". tableTwo will have columns "c", "d", "e"
set nonCoal to {"City", "Contact", "Names", {"Address 1", "Address", "address one", "Address1", "Text4", "Address 1"}, {"E-Mail", "E-Mail Address", "Email", "Email Address", "EmailAddress", "Email"}, {"Zip", "Zip Code", "ZipCode", "Zip"}, {"Telephone", "BusinessPhone", "Phone", "Work Phone", "Telephone"}, {"St", "State", "State"}, {"Salutation", "Mr/Ms", "Mr/s", "Salutations", "Sautation", "Salutation"}}
-- Build the COALESCE statements
repeat with h from 1 to count of nonCoal
set aColumn to item h of nonCoal
if class of aColumn is not list then
if [count of words of aColumn] > 1 then set aColumn to quote & aColumn & quote
set end of tempCol to aColumn
set end of permCol to aColumn
else
set coalEntry to {}
repeat with i from 1 to count of aColumn
set coalCol to item i of aColumn as string
if [count of words of coalCol] > 1 then set coalCol to quote & coalCol & quote
if i = 1 then
set end of coalEntry to "TRIM[COALESCE[" & coalCol & ", ''] || \" \" || "
else if i < [[count of aColumn] - 1] then
set end of coalEntry to "COALESCE[" & coalCol & ", ''] || \" \" || "
else if i = [[count of aColumn] - 1] then
set as_Col to item [i + 1] of aColumn as string
if [count of words of as_Col] > 1 then set as_Col to quote & as_Col & quote
set end of coalEntry to ["COALESCE[" & coalCol & ", '']] AS " & as_Col] & ""
set end of permCol to as_Col
end if
end repeat
set end of tempCol to [coalEntry as string]
end if
end repeat
-- Since there are ", '' within the COALESCE statement, you can't use "TID" and "as string" to convert tempCol and permCol for entry into sqlite3. I rebuild the lists in the next block.
repeat with j from 1 to count of tempCol
if j < [count of tempCol] then
set end of tempColEntry to item j of tempCol & ", "
set end of permColEntry to item j of permCol & ", "
else
set end of tempColEntry to item j of tempCol
set end of permColEntry to item j of permCol
end if
end repeat
set end of permColEntry to ", " & item [j + 1] of permCol
set permColEntry to [permColEntry as string]
set tempColEntry to [tempColEntry as string]
-- Create the new table with an "id integer primary key" column
set createTable to "create table " & tableTwo & " [" & permColEntry & "]; "
do shell script "sqlite3 " & databasePath & space & quoted form of createTable
-- Create a temporary table and then populate the permanent table
set createTemp to "create temp table placeholder as select " & tempColEntry & " from " & tableOne & "; " & "insert into " & tableTwo & " select Null, * from placeholder;"
do shell script "sqlite3 " & databasePath & space & quoted form of createTemp
--export the new table as a .csv file
do shell script "sqlite3 -header -column -csv " & databasePath & " \"select * from " & tableTwo & " ; \"> ~/" & tableTwo & ".csv"